Re: Informix SQL select time optimization

%UNIPLEX %TO informix-list@rmy.emory.edu %FROM jgordon %SYSTEM DHLNET %SUBJECT Re: Informix SQL select time optimization %VERIFY y %DATE 23/04/91 10:51 %REFERENCE 30946 >X-Informix-List-Id: <newsgate.56> > > 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. >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; Niklas, Unfortunately the query optimiser in 2.10 Informix is pretty stupid once it has found a method of doing the query it doesnt make any effort to look for a better way of doing it, nor does it provide any method for you to see what it is doing. Version 4 Informix not only tries to find the cheapest method of doing the query but also provides the statement 'SET EXPLAIN ON' to allow you to see how it constructed the query. This allows you to restructure the query and reindex until you get the best performance - You may need to reindex because with version 4 the order in which the indexes were created can be important. A couple of suggestions for your problem. 1 - As table E requires selection information from B and D do this selection last in your select statement at the moment it probably is doing sequential scans from B to E to D. Also perhaps you could try brackets round the selections to try and force the order of selection. I use brackets only to make the selects more readable I havent investigated what effect they have on the optimiser if any. Does anyone know? 2 - If this is for an ordered report or a similar grouped requirement where you are trying to pick up the ?text description? for a number of rows. Try taking out selections for the groups from the main selects 'DECLAREING' them seperately and doing fetches within the main processing loop only when the group changes. This not only saves a lot of temporary table space but is also considerably faster. For further information see David Chan's excellent article in EIUG Newsletter No 9 (August 1989) though I believe test 5 will crash out in some situations. If you dont have access I will try and get permission to reproduce on the net. Regards, Jim Name : Jim Gordon Internet Address: jgordon@lhr-sys.ssf-sys.DHL.COM Organisation : DHL Systems Ltd (UK) Address : Kings House, Great West Road, Brentford, Middx, UK TW8 9HS Telephone : (+44) 81 747 8330 Ext 318 %UEND