Re: Tuning

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 Now using the variable may not and most likely will not improve performance. (Unless someone at Informix screwed up ;-) But your biggest gain will be in creating the index and making the first statement use that index. -Mikey