I sincerely hope I am in the right place.





We have a query which takes 4 minutes 30 seconds to execute this is simply
too long. Please help !!!!!





I am going to list as much detail as possible





Firebird Version = WI-V2.5.2.26540 Firebird 2.5


Operating system = 32 bit Window 7 Ultimate


4 gig ram


Intel core 2 dual processor 2.4 Ghz








Below is the DDL for the table in question





SET SQL DIALECT 3;





SET NAMES NONE;





/***************************************************************************
***/


/**** Tables
****/


/***************************************************************************
***/








CREATE TABLE JOURNAL (


JOURID INTEGER,


JDATE DATE,


JTYPE INTEGER,


MODULE INTEGER,


TRXDATE DATE,


SOURCEID INTEGER,


SOURCE VARCHAR(40),


DEBITAMOUNT CURRENCYVALUE /* CURRENCYVALUE = NUMERIC(18,2) DEFAULT
0.00 */,


DESCRIPTION VARCHAR(254),


STAFFID INTEGER,


STATUS VARCHAR(30),


ACCNR INTEGER,


ACCCLASS INTEGER,


LEDGERTYPE VARCHAR(2),


CREDITAMOUNT CURRENCYVALUE /* CURRENCYVALUE = NUMERIC(18,2) DEFAULT
0.00 */,


DEPARTMENT INTEGER,


COMPANY INTEGER,


SOURCE2 VARCHAR(40),


CID INTEGER,


VSID INTEGER,


SUPPID INTEGER,


DDATE DATE,


SOURCENAME VARCHAR(100),


FPERIOD VARCHAR(7),


RECONCILED VARCHAR(5),


BATCH INTEGER,


HASVAT VARCHAR(5),


SERVICEADVISOR STAFFNR /* STAFFNR = INTEGER */,


TRANSNR INTEGER


);














/***************************************************************************
***/


/**** Indices
****/


/***************************************************************************
***/





CREATE INDEX JOURNAL_IDX1 ON JOURNAL (JDATE);


CREATE INDEX JOURNAL_IDX11 ON JOURNAL (FPERIOD);


CREATE INDEX JOURNAL_IDX12 ON JOURNAL (RECONCILED);


CREATE INDEX JOURNAL_IDX13 ON JOURNAL (SUPPID);


CREATE INDEX JOURNAL_IDX14 ON JOURNAL (CID);


CREATE INDEX JOURNAL_IDX15 ON JOURNAL (BATCH);


CREATE INDEX JOURNAL_IDX16 ON JOURNAL (SOURCE2);


CREATE INDEX JOURNAL_IDX18 ON JOURNAL (TRANSNR);


CREATE INDEX JOURNAL_IDX2 ON JOURNAL (JTYPE);


CREATE INDEX JOURNAL_IDX3 ON JOURNAL (ACCNR);


CREATE INDEX JOURNAL_IDX4 ON JOURNAL (MODULE);


CREATE INDEX JOURNAL_IDX5 ON JOURNAL (STATUS);


CREATE INDEX JOURNAL_IDX6 ON JOURNAL (SOURCE);





There are only 350,000 (350 Thousand)records in the table





Below is my actual sql query





select distinct







sum((case (extract (year from jdate )||''||extract ( month from jdate ))
when 20154 then (coalesce(invoicetotal,0)-coalesce(paid,0)) else 0 end)) as
Current_days,


sum((coalesce(invoicetotal,0)-coalesce(paid,0)))as totaldue









from (select (jdate)as jdate,





(Select COALESCE(sum(debitamount),0 )




from journal where accnr = '5995100' and suppid=j.suppid and
source2 = j.source2 and jdate = j.jdate)as paid,







(Select COALESCE(sum(creditamount),0 )




from journal where accnr = '5995100' and suppid=j.suppid and
source2 = j.source2 and jdate = j.jdate)as invoicetotal









from journal j









where j.jdate >= '2015/04/28'


and j.accnr = 5995100


and j.SUPPID = '1'


and (j.jtype = 2 or j.jtype = 99)


)







having sum((invoicetotal-paid))<>0




order by 2 asc








Please let me know if I should provide more information.





Regards





Stef





Email stef@...









[Non-text portions of this message have been removed]