Re: Tuning

Mike Segel wrote: : Jaroslaw Pyszkiewicz wrote: : > : > Does anybody have idea how to speed up such a querry. : > : > SELECT SUM(przydzial.godziny) : > INTO arr_przyd_sc[counter].suma_rok : > FROM przydzial : > WHERE YEAR(przydzial.miesiac)=YEAR(przyd_bufor.miesiac) : > AND przydzial.zad_id = przyd_bufor.zad_id : > : > This is description of table: : > "przydzial" : zad_id INTEGER, : > prac_id INTEGER, : > miesiac DATETIME YEAR TO MONTH, : > godziny INTEGER : FWIW, : 1) Create an index on zad_id if it does not already exist. : 2) It looks like przyd_bufor is a structure in the code not a : table. So try setting up a temp integer variable and setting : it to YEAR(przyd_bufor.miesiac) : 3) Re-write the statement to look like this: : : DEFINE foo INTEGER : foo = YEAR(przyd_bufor.miesiac : SELECT SUM(przydial.godziny) : INTO arr_przyd_sc[counter].suma_rok : FROM przydzial : WHERE przydzial.zad_id = przyd_bufor.zad.id : AND YEAR(przydzial.miesiac) = foo : One might also try taking YEAR completely out of the query, as follows: DEFINE yr DATETIME YEAR TO YEAR ,mfr,mto DATETIME YEAR TO MONTH LET yr = przyd_bufor.miesiac LET mfr = yr LET mto = mfr + 11 units month SELECT SUM(przydial.godziny) INTO arr_przyd_sc[counter].suma_rok FROM przydzial WHERE przydzial.zad_id = przyd_bufor.zad.id AND przydzial.miesiac >= mfr AND przydzial.miesiac <= mto If there is a composite index on zad_id and miesiac, the engine will more likely be able to use it to its fullest in this case. Alternately, the BETWEEN operator could be used. -- ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.strengur.is/~frantz/pow4gl.html