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 [email protected]
[Non-text portions of this message have been removed]
