niklas@appli.se (Niklas Hallqvist) (04/18/91)
Hello! I'm using Informix ESQL and have problems understanding how this product does it's queries. Very often when I've typed down a complex select statement I find that it gets real slooooow, in spite of the possibility of getting a fast keyed search. It seems that ISQL's heuristics fails to find the best search path throw the joins. Is there some way to find out these heuristics? If I knew them, I could always rearrange my select statements to reflect the search strategy ISQL imposes on them. The only thing I've discovered myself so far is that the order of the tables specified in the from clause is very important if there are several ways to go about the search. As an example to this problem, I have the following scenario right now: 5 tables A, B, C, D, E The relations between the tables are as follows: for every A-column there are many B:s, both A and B have an index on the column that the join is on. for every C-column there are many B:s, but only C has an index on the joined column. for every D-column there are many C:s, both C and D have an index on the column the join is on, D:s index is unique. (the C index is really a prefix of the composite index mentioned below) for every D-column there are many E:s, both D and E have an index on the column the join is on, D:s index is unique. (the E index is really a prefix of the composite index mentioned below) for every E-column there are many B:s, but only B has a unique index on the joined column. On top of this, there is a unique composite index in C on the columns which joins an C to a combination of B and D. There is a also unique composite index in E on the columns which joins an E to a combination of B and D. Of these tables only B is large (~ 400000 records) and the other ones contain just a couple of thousand records or less. The join between A and B are the one which is important as that's the one which cut down B's size to just some thousand records. What I want to do is this: select lots_of_fields from A, B, C, D, E where A.unique_index_of_A = $user_entered_variable and A.unique_index_of_A = B.unique_index_of_A_and_of_B_too and E.second_member_of_Es_index = B.second_member_of_Es_index and E.first_member_of_Es_index = D.first_unique_index_of_D and D.secod_unique_index_of_D = C.second_unique_index_of_D and C.unique_index_of_C = B.unique_index_of_C; Did you get that??? This query takes hours to complete, it should take minutes. Well actually I solved this using yet another temporary table just containing the joined rows of A, B and C, but thats space-expensive. Niklas -- Niklas Hallqvist Phone: +46-(0)31-40 75 00 Applitron Datasystem Fax: +46-(0)31-83 39 50 Molndalsvagen 95 Email: niklas@appli.se S-412 63 GOTEBORG, Sweden mcsun!sunic!chalmers!appli!niklas
llojd@earth.rivm.nl (Jan Diesel) (04/22/91)
In article <1356@appli.se> niklas@appli.se (Niklas Hallqvist) writes: > > Hello! > >I'm using Informix ESQL and have problems understanding how this >product does it's queries. Very often when I've typed down a complex >select statement I find that it gets real slooooow, in spite of the >possibility of getting a fast keyed search. It seems that ISQL's >heuristics fails to find the best search path throw the joins. Is >there some way to find out these heuristics? If I knew them, I could >always rearrange my select statements to reflect the search strategy >ISQL imposes on them. The only thing I've discovered myself so far >is that the order of the tables specified in the from clause is very >important if there are several ways to go about the search. > [ examples deleted ] Since it is virtually impossible to retrieve information concerning the Informix optimizer I have the nasty feeling there may not be anything like it in our Informix (version 2.1) product. Salespeople assured me version 4 *does* have such a feature. Nevertheless I found some hints regarding Informix performance in the Informix 'Tech Notes' publication of Spring 1989, and some useful information regarding Query Optimization in 'Tech Notes' of Spring 1987. Good luck, ------------------------------------------------------------------- Jan Diesel llojd@rivm.nl National Institute for Public Health and Environmental Protection Laboratory for Air Research P.O.Box 1, 3720 BA BILTHOVEN, The Netherlands. -------------------------------------------------------------------