Post reply

Tune slow query

  • July 9, 2009 at 7:55 am

    #131882

    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.

    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

  • July 9, 2009 at 8:36 am

    #1022419

    Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic750256-65-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply