Re: Tuning

jp@ciop.waw.pl (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 > It is used on small OnLine 4.1 with overloaded system but I was asked >to change code. Any suggestions ? As przyd_bufor isn't in the from clause it looks like przyd_bufor is a defined program record. In that case: Create an index on przydzial.miesiac Change the statement to: SELECT SUM(przydzial.godziny) INTO arr_przyd_sc[counter].suma_rok FROM przydzial WHERE przydzial.miesiac between mdy(1,1,YEAR(przyd_bufor.miesiac)) and mdy(12,31,YEAR(przyd_bufor.miesiac)) AND przydzial.zad_id = przyd_bufor.zad_id (If the mdy function can't be used in a select statement set up corresponding variables first - i don't remember of my head if mdy is available here, or is a 4GL function only.) This will enable the select to use the index on przydzial.miesiac. Whenever you have a function like year(przydzial.miesiac) in the where clause Informix isn't able to use an index on the column. You should therefore generally be very carefull about function calls in any where clause. Make sure it's evaluated only once, not for every row as in your example resulting in sequencial scans. May be you have to replace the mdy's also for this reason. Try it. As you select into an array it seems this select is part of something that is run inside a loop. In that case may be a select with a group by can be built to let the server do more of the work. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company