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.

score 0 · Answer 1 · answered Nov 25 '13 at 10:49

Difficult to say without haging an execution plan or some hints about number of rows in each table.

Replace this index

create nonclustered index #cx_tPotrazuje_1 on #tPotrazuje (Partija, Valuta, Referenca, Konto, pIznos)

by

create nonclustered index #cx_tPotrazuje_1 on #tPotrazuje (Partija, Valuta, Referenca, Konto, sIznos, pIznos)

score 0 · Answer 2 · answered Nov 25 '13 at 11:03

The creation of indexes is a very expensive process and it could be slow sometimes, according also to the workload of the instance and to the columns involved, for which an index is created.

Furthermore, it's very difficult to say what you need to optimise, without an execution plan and without know something about the data types of the columns involved in the indexes creation.

For example, the data types of the columns Partija, Referenca, Konto, Valuta, DatumValute are not so clear. You should tell us the data types of the columns involved in the creation of your indexes.

Thank you for your answer. Data types are: [Partija] [varchar](20), [Referenca] [varchar](60), [Konto] [varchar](10), [Valuta] [varchar](3), [DatumValute] [varchar](10) and P.pIznos, D.sIznos and P.Iznos are decimal(19,2). And I have no way to post you executoin plan. — Nina, Nov 25 '13 at 11:52

score 0 · Accepted Answer · answered Nov 25 '13 at 12:20

0

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.

answered Nov 25 '13 at 12:20

knb

9,138
4
58
85

Thank you... already talked to the client about this, and will try again to explain, but at this moment they wanna to use archived data... millions of them. But thanks anyway. – Nina Nov 25 '13 at 13:54
Then add a staging datatable (or a materialized view) with the two columns containing the intervals listing (I guess that's what the `...and P.pIznos < D.sIznos and D.sIznos <= P.Iznos` clause is doing) in a precomputed form. This data shouldn't sit in a temp table and to be thrown away after the query completes. I guess that this trick makes the query expensive as it forces the engine to compute a lot of values sequentially. – knb Nov 25 '13 at 16:25

How to optimise slow SQL query

3 Answers3