[I've removed irrelevant details from your original question]
>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 !!!!!
Definitely, Stef, most performance problems can be solved on this list, and I
agree that 4 and a half minute sounds long.
>CREATE TABLE JOURNAL (
> JDATE DATE,
> SOURCE2 VARCHAR(40),
> ACCNR INTEGER,
> SUPPID INTEGER,
> JTYPE INTEGER);
>CREATE INDEX JOURNAL_IDX1 ON JOURNAL (JDATE);
>CREATE INDEX JOURNAL_IDX13 ON JOURNAL (SUPPID);
>CREATE INDEX JOURNAL_IDX16 ON JOURNAL (SOURCE2);
>CREATE INDEX JOURNAL_IDX2 ON JOURNAL (JTYPE);
>CREATE INDEX JOURNAL_IDX3 ON JOURNAL (ACCNR);
>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.
The generated plan could have given more hints as to what is wrong, but I've
tried to rewrite your query anyway. So, could you see if the following query
gets the same result and whether or not it is any quicker?
with DateSource(jdate, source2) as
(select distinct jdate, source2
from journal
where jdate >= '2015/04/28'
and accnr = 5995100
and SUPPID = 1
and jtype in (2, 99)),
tmp(jdate, paid, invoicetotal) as
(select d.jdate, sum(debitamount), sum(creditamount)
from journal j
join DateSource d on j.jdate = d.jdate and j.source2 = d.source2
where j.accnr = 5995100
and j.SUPPID = 1
group by 1)
select sum(iif(jdate between '2015/04/01' and '2015/04/30', invoicetotal -
paid, 0) current_days,
sum(invoicetotal - paid) totaldue
from tmp
having sum(invoicetotal - paid) <> 0
order by 2
It would be interesting to learn whether or not this query is any quicker, and
if it isn't, the plans of both your original query and my modified version.
Please also check whether the modified query gets the correct result, there
may be subleties that I missed.
HTH,
Set
