How to optimise slow SQL query

Question

I need a help to optimise this query. In stored procedure this part is executed for 1 hour (all procedure need 2 to execute). Procedure works for a large amount of data. Query works with two temporary tables. Both use indexes:

create unique clustered index #cx_tDuguje on #tDuguje (Partija, Referenca, Konto, Valuta, DatumValute)
create nonclustered index #cx_tDuguje_1 on #tDuguje (Partija, Valuta, Referenca, Konto, sIznos)
create unique clustered index #cx_tPotrazuje on #tPotrazuje (Partija, Referenca, Konto, Valuta, DatumValute)
create nonclustered index #cx_tPotrazuje_1 on #tPotrazuje (Partija, Valuta, Referenca, Konto, pIznos)

And this is a query:

select D.Partija,  
       D.Referenca,  
       D.Konto,  
       D.Valuta,  
       D.DatumValute DatumZad,   
       NULLIF(MAX(COALESCE(P.DatumValute,@NextDay)), ,@NextDay) DatumUpl,  
       MAX(D.DospObaveze) DospObaveze,  
       MAX(D.LimitMatZn) LimitMatZn  
into #dwkKasnjenja_WNT 
from  #tDuguje D  
left join #tPotrazuje P on D.Partija = P.Partija  
                       AND D.Valuta = p.Valuta  
                       AND D.Referenca = p.Referenca   
                       AND D.Konto = P.Konto  
                       and P.pIznos < D.sIznos and D.sIznos <= P.Iznos  
WHERE 1=1  
    AND D.DatumValute IS NOT NULL  
GROUP BY D.Partija, D.Referenca, D.Konto, D.Valuta, D.DatumValute  

I have and Execution plan, but i am not enabled to post it here.

Answer 1

Just an idea: If you are permitted to do so, try to change the business logic first.

Maybe you constrain the result set only to include data from, say, a point in time back that is meaningful.

How far back in time do your account data reach? Do you really need to include all data all the way back from good old 1999?

Maybe you can say

D.DatumValute >= "Jan 1 2010"

or similar, in your WHERE clause

And this might create a much smaller temporary result set that is used in your complicated JOIN clause which will then run faster.

If you can't do this, maybe do a "select top 1000 ... order by datum desc" query, which might run faster, and then if the user really needs to , perfomr the slow running query in a second step.