CODEWITHSUNDEEP
sql-serversql-server-2000
JapNolt
JapNolt

Reputation: 120

Tune Slow SQL Query

I got an app running on my SQL Server that is starting to slow down on a specific task. I ran SQL Profiler and noticed that the following query is taking an enormous (1-2 minutes) amount of time. I don't have access to the code to change the query.
Is there anything I can tune/change in the database? The PC10000 table in the statement below has approx. 119000 records. I also have the execution plan attached.

SELECT TOP 25 
    zProjectID, zTaskID, zTransactionNumber, zTransactionDate, zUserID, 
    zCostCategoryDDL, zCostCategoryString, zSubCostCategory, zSubCostCategoryString, 
    zDepartmentID, zJournalEntry, zPostingDate, zSalesPostingDate, zPeriodNumber,
    zTransactionDescription, zBillingDescriptionLine1, zBillingDescriptionLine2, 
    zBillingDescriptionLine3, zBillingDescriptionLine4, zSalesAccountIndex, 
    zSalesAccountString, zDistDocumentTypeDDL, zDistDocumentNumber, zDistSequenceNumber,
    zSalesDocumentTypeDDL, zSalesDocumentNumber, zSalesLineNumber, zDistHistoryYear, 
    zSeriesDDL, zSourceDoc, zWebSource,    zOrigDocumentNumber, zOrigDocumentDate, 
    zOrigID, zOrigName, zExpenseStatusDDL, zApprovalUserIDCost, zAccountIndex,
    zAccountNumberString, zBillingStatusDDL, zApprovalUserIDBilling, zBillingWorkQty, 
    zBillingWorkAmt, zQty, zQtyBilled, zUnitCost,
    zUnitPrice, zRevenueAmt, zOriginatingRevenueAmt, zCostAmtEntered, zCostAmt, 
    zOriginatingCostAmt, zPayGroupID, zPayrollStatusDDL, zTotalTimeStatusDDL, 
    zEmployeeID, zHoursEntered, zHoursPaid, zPayRecord, zItemID, zItemDescription,
    zUofM, zItemQty, zBurdenStatusDDL, zUserDefinedDate, zUserDefinedDate2, 
    zUserDefinedString, zUserDefinedString2, zUserDefinedCurrency, 
    zUserDefinedCurrency2, zNoteIndex, zImportType, DEX_ROW_ID 
FROM 
    DBServer.dbo.pc10000 
WHERE 
    (zDistDocumentNumber in 
         (select cast(JRNENTRY as varchar(20)) 
          from DBServer..GL10001 
          where BACHNUMB = 'PMCHK00004283') 
    or zSalesDocumentNumber in 
         (select cast(JRNENTRY as varchar(20)) 
          from DBServer..GL10001 
          where BACHNUMB = 'PMCHK00004283')) 
ORDER BY 
   zProjectID ASC ,zTaskID ASC ,zTransactionNumber ASC

alt text

Upvotes: 0

Views: 1218

Answers (7)

Hiawatha Tiller
Hiawatha Tiller

Reputation:

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

Upvotes: 0

pjp
pjp

Reputation: 17629

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'))

Upvotes: 0

David Hedlund
David Hedlund

Reputation: 129792

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.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147234

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

Upvotes: 6

Justin Niessner
Justin Niessner

Reputation: 245429

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).

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 0

Dion Brown
Dion Brown

Reputation: 136

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.

Upvotes: 3

Related Questions