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