March 1, 2007 at 2:42 pm
I have identical structure and indexing on the tables involved in the following SQL statement. In development, it returns 975 records in under a second, with no table scans. In production, it takes 5 to 12 seconds to return data and includes 2 tables scans in the execution plan. Does anyone have any ideas about why?
Cross posted to performance tuning also
MAX(CAST(dbo.tblDocuments_Matrix_Levels.ViewPrint AS Int)) AS ViewPrint, MAX(CAST(dbo.tblDocuments_Matrix_Levels.Download AS Int))
AS Download, MAX(CAST(dbo.tblDocuments_Matrix_Levels.Notify AS Int)) AS Notify, dbo.tblDocuments.DocumentStr, dbo.tblDocuments.Del_DocString,
dbo.tblDocuments.ClientDocument_No, dbo.tblDocuments.SheetNo, dbo.tblDocuments.SheetNoTotal, dbo.tblDocuments.Title,
dbo.tblDocuments_Rev.Rev, dbo.tblDocuments_Rev.Rev_Date, dbo.tblRev_Purpose.Rev_Purpose_Code, dbo.tblDocuments_Rev.Filename,
dbo.tblFileExtensions.Extension
FROM dbo.tblRev_Purpose RIGHT OUTER JOIN
dbo.tblDocuments_Rev ON dbo.tblRev_Purpose.Rev_Purpose_ID = dbo.tblDocuments_Rev.Rev_Purpose_ID LEFT OUTER JOIN
dbo.tblFileExtensions ON dbo.tblDocuments_Rev.Extension_id = dbo.tblFileExtensions.Extension_ID RIGHT OUTER JOIN
dbo.tblDocuments_Matrix_Levels WITH (NOLOCK) INNER JOIN
dbo.tblDocuments WITH (NOLOCK) ON dbo.tblDocuments_Matrix_Levels.Project_ID = dbo.tblDocuments.Project_ID ON
dbo.tblDocuments_Rev.DocumentRev_ID = dbo.tblDocuments.Rel_Rev_ID
WHERE (dbo.tblDocuments_Matrix_Levels.L1_ID > 0) AND (dbo.tblDocuments.L1_ID = dbo.tblDocuments_Matrix_Levels.L1_ID) AND
(dbo.tblDocuments.Rel_Rev_ID <> 0) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L2_ID > 0) AND
(dbo.tblDocuments.L2_ID = dbo.tblDocuments_Matrix_Levels.L2_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L3_ID > 0) AND
(dbo.tblDocuments.L3_ID = dbo.tblDocuments_Matrix_Levels.L3_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L4_ID > 0) AND
(dbo.tblDocuments.L4_ID = dbo.tblDocuments_Matrix_Levels.L4_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L5_ID > 0) AND
(dbo.tblDocuments.L5_ID = dbo.tblDocuments_Matrix_Levels.L5_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L6_ID > 0) AND
(dbo.tblDocuments.L6_ID = dbo.tblDocuments_Matrix_Levels.L6_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L7_ID > 0) AND
(dbo.tblDocuments.L7_ID = dbo.tblDocuments_Matrix_Levels.L7_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L8_ID > 0) AND
(dbo.tblDocuments.L8_ID = dbo.tblDocuments_Matrix_Levels.L8_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.L9_ID > 0) AND
(dbo.tblDocuments.L9_ID = dbo.tblDocuments_Matrix_Levels.L9_ID) OR
(dbo.tblDocuments.Rel_Rev_ID <> 0) AND (dbo.tblDocuments_Matrix_Levels.Document_ID > 0) AND
(dbo.tblDocuments.Document_ID = dbo.tblDocuments_Matrix_Levels.Document_ID)
GROUP BY dbo.tblDocuments_Matrix_Levels.Name_ID, dbo.tblDocuments.Document_ID, dbo.tblDocuments.Del_DocString,
dbo.tblDocuments.ClientDocument_No, dbo.tblDocuments.SheetNo, dbo.tblDocuments.SheetNoTotal, dbo.tblDocuments.Title,
dbo.tblDocuments.DocumentStr, dbo.tblDocuments_Rev.Rev, dbo.tblDocuments_Rev.Rev_Date, dbo.tblDocuments_Rev.Filename,
dbo.tblRev_Purpose.Rev_Purpose_Code, dbo.tblFileExtensions.Extension, dbo.tblDocuments.DocBook, dbo.tblDocuments.Project_ID
HAVING (dbo.tblDocuments_Matrix_Levels.Name_ID = 1038)
March 2, 2007 at 2:29 am
Hi,
I'd need more information to be able to comment on this.
1) You get the difference in dev and live - Are the data volumns the same in both environments?
2) Have you checked the fragmentation/stats of the indexes?
3) Are both instanaces set up in excalty the same way?
4) Also are you running the query in the same way on both instances i.e. stored procedure dynamic sql etc...
Sorry if you've already gone through these but it helps get a better idea on what might be going wrong.
I'd probably start with the tables that are being scanned in production and compare those to the development environment. Then work from there.
Not much help at this stage I know
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply