-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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))))


all indexes seems to be used but the same query for a longer range of
days and Z.GODZINA (which is in fact range in hours) - it takes more
then 15 minutes to complete.

SPRZEDAZ_IDX_DATA is a compound index on (DATASPRZEDAZY,GODZINA,NRDOK)
because DATASPRZEDAZY (DATETIME type but time part is always 00:00 - it
was imported from another application) has
a little low selectivity alone (due to fact that there are plenty of
records for
each date)


Below is metadata:




CREATE TABLE SPRZEDAZ (
ID PK /* PK = BIGINT NOT NULL */,
IDMAG INTEGER NOT NULL,
IDKONTRH FK /* FK = BIGINT */,
TYP TYPDOK /* TYPDOK = VARCHAR(5) NOT NULL */,
NRDOK FK /* FK = BIGINT */,
WYSTAWIL USERID /* USERID = CHAR(20) DEFAULT USER NOT NULL */,
DATAOPERACJI STAMP /* STAMP = TIMESTAMP DEFAULT 'NOW' NOT NULL */,
DATASPRZEDAZY STAMP /* STAMP = TIMESTAMP DEFAULT 'NOW' NOT NULL */,
GODZINA SMALLINT DEFAULT 0 NOT NULL,
OSOBA VARCHAR(50) COLLATE PXW_PLK,
TERMINPL DATE NOT NULL,
RODZAJPL INTEGER NOT NULL,
NRKASY INTEGER DEFAULT NULL,
NRKASJERA INTEGER DEFAULT NULL,
NAZWAKASJERA VARCHAR(20) DEFAULT NULL COLLATE PXW_PLK,
FISKALNY BOOL /* BOOL = CHAR(1) NOT NULL CHECK (VALUE IN
('T','N')) */,
RABAT DECIMAL(18,4) DEFAULT 0 NOT NULL,
AKTYWNY AKTYWNYDOK /* AKTYWNYDOK = CHAR(1) DEFAULT 'A' NOT
NULL CHECK (VALUE IN ('A','B','U')) */,
UWAGI KOMENTARZ /* KOMENTARZ = VARCHAR(255) DEFAULT NULL */,
KOMENTARZ KOMENTARZ /* KOMENTARZ = VARCHAR(255) DEFAULT NULL */,
MARKER MARKER /* MARKER = CHAR(1) DEFAULT 'M' NOT NULL CHECK
(VALUE IN('M','O')) */,
GUID VARCHAR(30) DEFAULT NULL COLLATE PXW_PLK
);




/* Check constraints definition */

ALTER TABLE SPRZEDAZ ADD CHECK ((GODZINA >= 0) AND (GODZINA <= 24));

ALTER TABLE SPRZEDAZ ADD CONSTRAINT SPRZEDAZ_PK PRIMARY KEY (ID);

CREATE INDEX SPRZEDAZ_IDX_DATA ON SPRZEDAZ (DATASPRZEDAZY, GODZINA, NRDOK);





CREATE TABLE POZSPRZEDAZ (
ID PK /* PK = BIGINT NOT NULL */,
IDSPR FK NOT NULL /* FK = BIGINT */,
IDTOW FK NOT NULL /* FK = BIGINT */,
IDLOK INTEGER DEFAULT NULL,
CZNETTO CENA /* CENA = DECIMAL(18,4) DEFAULT 0 NOT NULL CHECK
(VALUE >= 0) */,
CSNETTO CENA /* CENA = DECIMAL(18,4) DEFAULT 0 NOT NULL CHECK
(VALUE >= 0) */,
CSBRUTTO CENA /* CENA = DECIMAL(18,4) DEFAULT 0 NOT NULL CHECK
(VALUE >= 0) */,
ILOSC ILOSC NOT NULL /* ILOSC = DECIMAL(18,3) DEFAULT 0 */,
VATS VAT NOT NULL /* VAT = INTEGER NOT NULL */,
VATZ VAT NOT NULL /* VAT = INTEGER NOT NULL */,
RABAT PROCENT /* PROCENT = NUMERIC(4,2) DEFAULT 0 NOT NULL CHECK
(VALUE BETWEEN 0 AND 100) */
);



ALTER TABLE POZSPRZEDAZ ADD CONSTRAINT POZSPRZEDAZ_PK PRIMARY KEY (ID);

ALTER TABLE POZSPRZEDAZ ADD CONSTRAINT FK_POZSPRZEDAZ_IDSPR FOREIGN KEY
(IDSPR) REFERENCES SPRZEDAZ (ID) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE POZSPRZEDAZ ADD CONSTRAINT FK_POZSPRZEDAZ_TOW FOREIGN KEY
(IDTOW) REFERENCES TOWARY (ID) ON UPDATE CASCADE;








Best Regards
Boguslaw Brandys

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCxXk1tuGICzHOh+YRAtRqAJ90Olmc2UtNAfbG0CI2jKza6ww7QgCfWs2I
c0S0dQij++NfKnbY3wKcsFw=
=iED8
-----END PGP SIGNATURE-----