SQL Server Performance

Tune slow query

Discussion in 'Performance Tuning for DBAs' started by JapNolt, Jul 9, 2009.

  1. JapNolt New Member

    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.
    Code:
    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 GP01.dbo.pc10000 
    WHERE (zDistDocumentNumber in (select cast(JRNENTRY as varchar(20)) from GP01..GL10001 where BACHNUMB = 'PMCHK00004283') 
    or zSalesDocumentNumber in (select cast(JRNENTRY as varchar(20)) from GP01..GL10001 where BACHNUMB = 'PMCHK00004283')) 
    ORDER BY zProjectID ASC ,zTaskID ASC ,zTransactionNumber ASC
  2. Adriaan New Member

    You have two subqueries that have CAST(column AS varchar(20)).
    I doubt that you actually need the CAST() - if you do, this is a flaw in the design of the two tables.
    What is worse is that the CAST is preventing SQL Server from using an index on those columns. This may not have been a big problem so long as the lookup tables contained a smaller amount of data.
  3. FrankKalis Moderator

    Welcome to the forum!
    OR conditions in a WHERE clause can very easily kill performance. But if you haven't the permissions to change the code anyway, about the only thing you can do is to see if you can improve performance by adding and/or changing an index.
    119000 rows is nothing, but how many rows does GL10001 contain?
  4. moh_hassan20 New Member

    also,
    you use In (.. ) cluse in the where condition which is a killin performance which lead to table /index scan
    the size of the data returned by query is large , which may consume cache
    Can you post the size of the table used in the query using sp_spaceused
  5. JapNolt New Member

    OK, I noticed that an index was missing on the GL10001 table so I added that and it seemed to speed up the query in my tests. I am waiting to hear back from the users to see if they notice a difference.

Share This Page