CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
jwdv22
jwdv22

Reputation: 33

Erratic query performance

I am new to this site, but please don't hold it against me. I have only used it once.

Here is my dilemma: I have moderate SQL knowledge but am no expert. The query below was created by a consultant a long time ago.

On most mornings it takes a 1.5 hours to run because there is lots of data. BUT other mornings, it takes 4-6 hours. I have tried eliminating any jobs that are running. I am thoroughly confused as to what to try to find out what is causing this problem.

Any help would be appreciated.

I have already broken this query into 2 queries, but any tips on ways to help boost performance would be greatly appreciated.

This query builds back our inventory transactions to find what our stock on hand value was at any given point in time.

SELECT     
    ITCO, ITIM, ITLOT, Time, ITWH, Qty, ITITCD,ITIREF, 
    SellPrice, SellCost,
    case 
        when Transaction_Cost is null 
        then Qty * (SELECT ITIACT 
                    FROM (Select Top 1 B.ITITDJ, B.ITIREF, B.ITIACT
                          From OMCXIT00 AS B 
                          Where A.ITCO = B.ITCO
                          AND A.ITWH = B.ITWH
                          AND A.ITIM = B.ITIM
                          AND A.ITLOT = B.ITLOT 
                          AND ((A.ITITDJ > B.ITITDJ) 
                               OR (A.ITITDJ = B.ITITDJ AND A.ITIREF <= B.ITIREF))
                          ORDER BY B.ITITDJ DESC, B.ITIREF DESC) as C) 
        else Transaction_Cost
    END AS Transaction_Cost, 
    case when ITITCD = 'S' then ' Shipped - Stock' else null end as TypeofSale,
    case when ititcd = 'S' then ITIREF else null end as OrderNumber
FROM  
    dbo.InvTransTable2 AS A

Here is the execution plan. https://i.sstatic.net/evw9u.png Execution plan bottle neck on sort I think

Here is the DTA but I am unsure how to read it since the recommedations are blank. Shouldn't that say "Create"? https://i.sstatic.net/BAbq0.png

Upvotes: 1

Views: 251

Answers (2)

Tonci Korsano
Tonci Korsano

Reputation: 26

besides adding indexes to change table scans for index seeks, ask to yourself: "do i really need this order by in this sql code?". if you dont neet this sorting, remove order by from your sql code. next, there is a good chance your code will be faster.

Upvotes: 0

Farfarak
Farfarak

Reputation: 1517

You can not do match with dbo.InvTransTable2, because of you are selected all records from it, so it will be left scanning records.

  1. Make sure that you have clustered index on OMCXIT00, it looks like it is a heap, no clustered index. Make sure that clustered index is small, but has more distinct values in it.
  2. If you have not many records OMCXIT00, it may be sufficient to create index with key ITCO and include following columns in include ( ITITDJ , ITIREF, ITWH,ITCO ,ITIM,ITLOT )

Index creation example:

   CREATE INDEX IX_dbo_OMCXIT00 
                     ON OMCXIT00 ([ITCO])
                    INCLUDE ( ITITDJ , ITIREF)

  1. If it does not help, then you need to see which columns in the predicates that you are searching for has more distinct values, and create index with key one or some of them and make sure reorder predicate order in where clause.

                      A.ITCO = B.ITCO
                  AND A.ITWH = B.ITWH
                  AND A.ITIM = B.ITIM
                  AND A.ITLOT = B.ITLOT

Upvotes: 2

Related Questions