Hi Boguslaw!

I doubt you can improve your query much. Logically,

Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-01'
AND ((Z.GODZINA BETWEEN 8 AND 8) OR Z.GODZINA=0))

belongs in the WHERE clause and not in the JOIN clause, but that's the
only thing I see that ought to be changed in the SQL and it shouldn't
help solving your problem, because the plan seems just fine. Though
wait a minute! Your plan mentions two tables (CM and CT) that are not
in your query! Why???

You could of course try to remove the GODZINA field from your
SPRZEDAZ_IDX_DATA index, though my guess is that it would not improve
the execution speed (though it is worth a try).

I assume adding

'WHERE P.ILOSC > 0 [AND|OR] (P.CSNETTO > 0)'

will change the result your query?

I must admit that I will be surprised if the speed increases more than
the range, I can understand that if one day takes 30 seconds, then one
month will take 15 minutes, but if it increases more than that, then
I'd say it ought to be investigated.

Sorry for not being able to be of more help,
Set

--- In firebird-support@yahoogroups.com, Bogus³aw Brandys wrote:
> Hello,
>
> I'd like to ask if is something to be done to speed my query up ?
> Query below is fast enough (about few seconds) only when I set a day
> range not wider.
>
> SELECT M.OPIS AS MAGAZYN,
> CAST(T.KODKRESKOWY AS INTEGER) AS KOD,T.NAZWA AS NAZWA,
> SUM(p.ILOSC) AS ILOSC,
> CAST(P.CZNETTO AS DECIMAL(18,2)) AS CZNETTO,
> CAST(SUM(P.CZNETTO*P.ILOSC) AS DECIMAL(18,2)) AS WZNETTO,
> CAST(P.CSBRUTTO AS DECIMAL(18,2)) AS CSBRUTTO,
> CAST(SUM(P.CSNETTO*P.ILOSC) AS DECIMAL(18,2)) AS WSNETTO,
> CAST(SUM(P.CSBRUTTO*P.ILOSC) AS DECIMAL(18,2)) AS WSBRUTTO,
> CAST((100*(SUM(P.CSNETTO*P.ILOSC) -
> SUM(P.CZNETTO*P.ILOSC))/(SUM(P.CSNETTO*P.ILOSC))) AS DECIMAL(4,2))
> AS PROC_MARZY,
> CAST((SUM(P.CSNETTO*P.ILOSC) - SUM(P.CZNETTO*P.ILOSC))
> AS DECIMAL(18,2)) AS MARZA
> FROM POZSPRZEDAZ P
> INNER JOIN SPRZEDAZ Z ON (P.IDSPR = Z.ID AND
> Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-01'
> AND ((Z.GODZINA BETWEEN 8 AND 8) OR Z.GODZINA=0))
> INNER JOIN TOWARY T ON (P.IDTOW=T.ID)
> INNER JOIN MAGAZYNY M ON (Z.IDMAG=M.ID)
> GROUP BY M.OPIS,T.KODKRESKOWY,T.NAZWA,P.CZNETTO,P.CSBRUTTO
> HAVING SUM(P.ILOSC*P.CSNETTO) <> 0
> ORDER BY M.OPIS,T.NAZWA
>
> PLAN (CM INDEX (PK_CECHY_MAG))
> PLAN (CT INDEX (PK_CECHY_TOW))
> PLAN SORT (SORT (JOIN (Z INDEX (SPRZEDAZ_IDX_DATA),M INDEX
> (MAGAZYNY_PK),P INDEX (FK_POZSPRZEDAZ_IDSPR),T INDEX (PK_TOWARY))))