Tune Slow SQL Query

Answer 1

The query is doing 2 table scans on the GL10001 table. From a quick look at the query (which is a bit hard to read) I would see if you have an index on the BACHNUMB column.

Answer 2

You could rewrite those sub-selects as a join, and add an index to GP01..GL10001 on BACHNUMB and JRNENTRY

Answer 3

Since you can't change the query, the best thing you could do is make sure you have indexes on the columns that you're using for your joins (and subqueries). If you can think of a better query plan, you could provide that to SQL Server instead of letting it calculate its own (this is a very rare case).

Answer 4

The biggest problem you have looks to be due to lack of suitable indexes. You can see that because of the presence of Table Scans within the execution plan.

Table Scans hit performance as they mean the whole table is being scanned for data that matches the given clauses in the query.

I'd recommend you add an index on BACHNUMB in GL10001

You may also want to try indexes on zDistDocumentNumber and zSalesDocumentNumber in PC10000, but I think the GL10001 index is the main one.

"IN" clauses are typically quite expensive compared to other techniques, but as you can't change the query itself then there's nothing you can do about that.

Without a doubt, you need to add suitable indexes

Answer 5

the execution plan shows pretty clearly that actually locating the rows is what's taking all the time (no cumbersome bookmark lookups, or aggregation/rearrange tasks), so it's quite positively going to be a question of indexing. hover the table scans in the execution plan, and check 'object' in the tooltip, to see what columns are being used. see to it that they're indexed.

you might also want to run a trace to sample some live data, and feed that to the database tuning advisor.

Answer 6

Replace the OR with a UNION ALL of two queries this should get shot of those spools

i.e. run the query once with something like this

SELECT ....

(zDistDocumentNumber in 
     (select cast(JRNENTRY as varchar(20)) 
      from DBServer..GL10001 
      where BACHNUMB = 'PMCHK00004283') 

UNION ALL

SELECT ...

zSalesDocumentNumber in 
     (select cast(JRNENTRY as varchar(20)) 
      from DBServer..GL10001 
      where BACHNUMB = 'PMCHK00004283'))

Answer 7

In addition to adding indexes, you can also convert the IN statements to EXISTS... something along these lines:

    SELECT TOP 25 ....
FROM GP01.dbo.pc10000 parent
WHERE EXISTS
    (
    SELECT child.*
    FROM GP01..GL10001 child
    WHERE BACHNUMB = 'PMCHK00004283'
     and parent.zDistDocumentNumber = child.JRNENTRY
    )
    OR EXISTS
    (
    SELECT child2.*
    FROM GP01..GL10001 child2
    WHERE BACHNUMB = 'PMCHK00004283'
     and parent.zSalesDocumentnumber = child2.JRENTRY
    )
ORDER BY zProjectID ASC ,zTaskID ASC ,zTransactionNumber ASC