Hi,
I'm on Windows 7, running Firebird 3.0.4 :
I have this table hosting 1475813 records :
CREATE TABLE JOURNAL_CAISSE (
UID UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
NUMERO INTEGER NOT NULL,
TYPE_MOUVEMENT INTEGER NOT NULL,
DATEHEURE TIMESTAMP DEFAULT 'NOW' NOT NULL,
MAGASIN UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
CAISSE UNIQUEID NOT NULL /* UNIQUEID = CHAR(16) */,
PERIODE INTEGER NOT NULL,
ANNULE BOOLEAN DEFAULT false NOT NULL,
QUANTITE INTEGER DEFAULT 1 NOT NULL,
TYPE_REGLEMENT VARCHAR(30),
DEVISE CHAR(12),
VALEUR DECIMAL(10,4) DEFAULT 0.00 NOT NULL
);
With these constraints/indices :
ALTER TABLE JOURNAL_CAISSE
ADD CONSTRAINT JOURNAL_CAISSE_PK
PRIMARY KEY (UID);
ALTER TABLE JOURNAL_CAISSE
ADD CONSTRAINT JOURNAL_CAISSE_PERIODE_FK
FOREIGN KEY (PERIODE)
REFERENCES PERIODES (ID)
ON UPDATE CASCADE;
ALTER TABLE JOURNAL_CAISSE
ADD CONSTRAINT JOURNAL_CAISSE_TYPES_FK
FOREIGN KEY (TYPE_MOUVEMENT)
REFERENCES TYPES_MVT_JOURNAL (TYPE_ID)
ON UPDATE CASCADE;
CREATE INDEX JOURNAL_CAISSE_CAISSE ON JOURNAL_CAISSE (
TYPE_MOUVEMENT, CAISSE, DATEHEURE
);
CREATE INDEX JOURNAL_CAISSE_IDX ON JOURNAL_CAISSE (
PERIODE, MAGASIN, ZONE, CAISSE, NUMERO
);
I can't find a way to index this table properly so that this query can run
quickly (for now it takes around 45s :
select
cast(2 as Integer) as TYPE_MVT,
TYPE_REGLEMENT, DEVISE, null as BANQUE,
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3))
and (ANNULE is false)
and (
(magasin=x'825469DD62BB4583B9F75C93E85FD4BF')
or (magasin=x'27917E1E9E99469ABB3B6B82075AD52A')
or (magasin=x'4AFB0F29F91644E8AC15B980FC4A17CF')
or (magasin=x'872CA352AD09459694F88FE51324CB35')
or (magasin=x'A2C3F9F5D1B1404293589068A8297EC0')
or (magasin=x'3050631347F04776BA6C08E10F164D76')
or (magasin=x'D277CB68C3154EACBC97B4BF95CC5B8F')
or (magasin=x'CA20DC918C9145FDA797043DCF477EA5')
or (magasin=x'79E0F6FE896A41F99D44D219A63CB511')
or (magasin=x'82717E0DA00D4DE2B82415A03F72846F')
or (magasin=x'189886B18FCD4998BEC33297ABF79E47')
or (magasin=x'376C73922868406CBD39540D47C7C1A7')
or (magasin=x'54C4FC9317724996989DC1C2BCA800CC')
or (magasin=x'C92CE52C9FFE48EA8B510888A0BD419C')
or (magasin=x'4B70E2D2232D47A6A74CE5C20ECDC941')
or (magasin=x'C893800B9A2E4E42BC34C47DEFF7135A')
or (magasin=x'87DA11B6C9724B47808B89E7338E9DFE')
)
and (
(caisse=x'0C949D9560234EE7B4824ABB93DA2386')
or (caisse=x'0F84C9F9A1E54C9C83AA1CEC3D337E9A')
or (caisse=x'19CFC91E81444B538640B4ED208D6AAC')
or (caisse=x'1B80E288A79C4569BEFE46D1FBD116B4')
or (caisse=x'22B06AC867AA4C87B813C78992105569')
or (caisse=x'29261F9D1B784723B7B474F056CE140C')
or (caisse=x'2AD2EEB3020641B28F53F03216169E36')
or (caisse=x'2BEB18A58A6F4EE19DA256B7E5A2C35E')
or (caisse=x'2D19A90D25AC4019B708D7A133A0FBE5')
or (caisse=x'2D7735DB379948A6A023C343A04E049C')
or (caisse=x'34402C4342474DBB8CB0446A09FEA15A')
or (caisse=x'3825FBA5A4B348EE9F604B2A2C994061')
or (caisse=x'396CF8284423444BA2509393BEDFD3B4')
or (caisse=x'3A3B742C2D394F589AA5EABAD0733B84')
or (caisse=x'3D53981F65E94100AE3779017C23F8A2')
or (caisse=x'448C6D67543A45859D7BAD5778D30DD9')
or (caisse=x'5996EAA0B442440C9C563BC4399F317C')
or (caisse=x'5A98039B179643238250E1BEB73F8FD6')
or (caisse=x'5F1357BFE2E64D309FC6889C07348A8A')
or (caisse=x'6D33F33594864CF2B32487797AC5D8A8')
or (caisse=x'6FF591B1D10C4A4C8C0904B629C42731')
or (caisse=x'70ABF3DAC8774484A172CBC8B65A98B4')
or (caisse=x'796C49B2D52B465A95D5B5D04EB43CEC')
or (caisse=x'7B729EC0919643DF8197407535B27DAF')
or (caisse=x'7F00F7E2FCD64946BF6C3C1DAE1D5716')
or (caisse=x'8579CAD9D7DC4C61B172F10D4E748891')
or (caisse=x'86F8AE15F55D40AF9E9C2D74B73C58FB')
or (caisse=x'8A63C3B11F774DC3AD599DE880FE5770')
or (caisse=x'8CFA2F21D96D49E3857E302542535C7A')
or (caisse=x'90495B8778D6482FA11D4BCA97629FDD')
or (caisse=x'909D44F58DF24077B51E3BB493E50C42')
or (caisse=x'9166B3D7319741669AB7DD434645426D')
or (caisse=x'936537DC96694885A6F7E661D3490C67')
or (caisse=x'9374E2AF14D749A2A2DAE63918A51D05')
or (caisse=x'985690F55F4244DAAED2063DB9A9EF08')
or (caisse=x'9AD383E3E6E842668481AFB3069C3812')
or (caisse=x'9CAA834580EA4B209DE4FBEC223AFE05')
or (caisse=x'A1CB7A2BB3B6465DB167216298EB45FD')
or (caisse=x'A42802A9027F4D8ABFDD4EBAEDC84A16')
or (caisse=x'ABE44B53C5454F12AE94925DEBB084F5')
or (caisse=x'AD068CF02DE54701AE2D572821B4F003')
or (caisse=x'B1B535D246554BA28EAC2F0BA6C50A6A')
or (caisse=x'B457957BFC55458C99B65C0471DC3CE6')
or (caisse=x'BBC68C6092924E7FB107DAA026B96BE6')
or (caisse=x'C29FE4826F014120BD451692052AA3ED')
or (caisse=x'C548320C0F094E8DBD0A0BE81D8754F3')
or (caisse=x'C5972AD1B56943C3A9612AC296167888')
or (caisse=x'D0454F093920400598DB91584A520CE1')
or (caisse=x'D33896B26CA64CCC8B0FDB6EEDE8005A')
or (caisse=x'D587BF15FA614573A7587D019C793B96')
or (caisse=x'D6F1C25294B042D7BF8BEA18AEF2EAC2')
or (caisse=x'DBE9AE3227874A69A49604BF34260169')
or (caisse=x'DE98446FCCE641278452009EAB8524F0')
or (caisse=x'E0858C2BF0D3442DA4DFFC461F32F804')
or (caisse=x'E147E1980D1343EA8DC46DD16B69ED1A')
or (caisse=x'E2CE21362B3D4038B2156BFA09A1BE34')
or (caisse=x'E4CFC80D32EE40578C40ACAC38056E50')
or (caisse=x'E9EC8A8305604952A1B21C9E01E25168')
or (caisse=x'EA5C17489E6C4461A5A4341960BB96C4')
or (caisse=x'EE5DA9CE231B4857B8DA603E01CB7AC2')
or (caisse=x'F4535DE4AA564132A9DE7CBC810940EB')
or (caisse=x'F45F75F18C14422081A0214E85A5D119')
or (caisse=x'F56F72D0221A435BB961A6F00926A5CB')
or (caisse=x'F59EFA9D34144818B1982213B3A767FD')
or (caisse=x'F5E09A25B6EA4EBD9DE291908E604655')
)
and (DATEHEURE between '2019-10-01' and '2019-11-01')
group by 1,2,3,4
Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_TYPES_FK,
JOURNAL_CAISSE_TYPES_FK))
While this query returns in 313ms :
select
cast(2 as Integer) as TYPE_MVT,
TYPE_REGLEMENT,
DEVISE, null as BANQUE,
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3))
and (ANNULE is false)
and (PERIODE in (8851,8850,8849,8847,8846,8845,8844,8843,8842,8840))
group by 1,2,3,4
Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_PERIODE_FK,
JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK,
JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK,
JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK,
JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_PERIODE_FK,
JOURNAL_CAISSE_PERIODE_FK, JOURNAL_CAISSE_TYPES_FK,
JOURNAL_CAISSE_TYPES_FK))
First query runs in 15s instead of 45s (yes it is three times faster...) if
I add this index :
CREATE INDEX JOURNAL_CAISSE_TYPE_DATEHEURE
ON JOURNAL_CAISSE (TYPE_MOUVEMENT, ANNULE, DATEHEURE);
Plan is : PLAN SORT (JOURNAL_CAISSE INDEX (JOURNAL_CAISSE_TYPE_DATEHEURE,
JOURNAL_CAISSE_TYPE_DATEHEURE))
But I would like to know if you can help me to optmize filtering on CAISSE
and MAGASIN columns.
Many thanks for your help, regards,
--
Pierre Yager
