First I would like to thank you all for your replies. It took me a bit to digest all of the information, and read the articles on Parameter Sniffing.
After clearing the cache (DBCC FREEPROCCACHE ), and trying to force recompile (WITH RECOMPILE) I am not seeing any improvement. I did run the UPDATE STATISTICS first as suggested by Elizabeth, and I also tried altering the procedure to use local variables as described in the Parameter Sniffing articles suggested by Gail. Still no improvement. Below is the procedure code, and the output from STATISTICS TIME & STATISTICS IO.
I ran both in the SQL Analyzer with Show Execution Plan, and they are very much different. You can view screen shots here. If there's a better way to output the plan in 2K please let me know.
Patrick
CREATE PROCEDURE dbo.GetDetailEquipTypes
@REPORTID int = NULL,
@BUCKETTOISSUEID int = NULL,
@SHOWID int = NULL,
@SITEID int
AS
SELECT DISTINCT ET.iId, ET.cName, C.cCategoryName
FROM tbl_ReportIssues RI FULL OUTER JOIN
tbl_EquipmentTypes ET INNER JOIN
tbl_Categories C ON ET.iCategoryId = C.iCategoryId INNER JOIN
tbl_Issues I ON ET.iCategoryId = I.iCategoryId INNER JOIN
tbl_EquipTypeToShow ETS ON ET.iId = ETS.iEquipTypeId INNER JOIN
tbl_BucketsToIssues BI ON I.iIssueId = BI.iIssueId LEFT OUTER JOIN
tbl_ReportIssueDetails RID ON ETS.iEquipTypeId = RID.iEquipTypeId ON RI.iReportIssueId = RID.iReportIssueId
AND RI.iBucketToIssueId = BI.iBucketToIssueId
WHERE (ET.iSiteId = @SITEID) AND (ETS.iShowId = @SHOWID OR @SHOWID IS NULL)
AND (BI.bDeleted = 0) AND (ETS.bDeleted = 0) AND (BI.iBucketToIssueId = @BUCKETTOISSUEID OR @BUCKETTOISSUEID IS NULL)
OR (ET.iSiteId = @SITEID) AND (ETS.iShowId = @SHOWID OR @SHOWID IS NULL)
AND (BI.iBucketToIssueId = @BUCKETTOISSUEID OR @BUCKETTOISSUEID IS NULL)
AND (RI.iReportId = @REPORTID OR @REPORTID IS NULL)
ORDER BY ET.cName
GO
Here is using local variables
CREATE PROCEDURE dbo.GetDetailEquipTypes
@REPORTID int = NULL,
@BUCKETTOISSUEID int = NULL,
@SHOWID int = NULL,
@SITEID int
AS
DECLARE @locREPORTID int
DECLARE @locBUCKETTOISSUEID int
DECLARE @locSHOWID int
DECLARE @locSITEID int
SET @locREPORTID = @REPORTID
SET @locBUCKETTOISSUEID = @BUCKETTOISSUEID
SET @locSHOWID = @SHOWID
SET @locSITEID= @SITEID
SELECT DISTINCT ET.iId, ET.cName, C.cCategoryName
FROM tbl_ReportIssues RI FULL OUTER JOIN
tbl_EquipmentTypes ET INNER JOIN
tbl_Categories C ON ET.iCategoryId = C.iCategoryId INNER JOIN
tbl_Issues I ON ET.iCategoryId = I.iCategoryId INNER JOIN
tbl_EquipTypeToShow ETS ON ET.iId = ETS.iEquipTypeId INNER JOIN
tbl_BucketsToIssues BI ON I.iIssueId = BI.iIssueId LEFT OUTER JOIN
tbl_ReportIssueDetails RID ON ETS.iEquipTypeId = RID.iEquipTypeId ON RI.iReportIssueId = RID.iReportIssueId
AND RI.iBucketToIssueId = BI.iBucketToIssueId
WHERE (ET.iSiteId = @locSITEID) AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)
AND (BI.bDeleted = 0) AND (ETS.bDeleted = 0) AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)
OR (ET.iSiteId = @locSITEID) AND (ETS.iShowId = @locSHOWID OR @locSHOWID IS NULL)
AND (BI.iBucketToIssueId = @locBUCKETTOISSUEID OR @locBUCKETTOISSUEID IS NULL)
AND (RI.iReportId = @locREPORTID OR @locREPORTID IS NULL)
ORDER BY ET.cName
GO
Output from STATISTICS
Running from stored procedure
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(9 row(s) affected)
Table 'tbl_EquipTypeToShow'. Scan count 1, logical reads 143, physical reads 0, read-ahead reads 0.
Table 'tbl_EquipmentTypes'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0.
Table 'tbl_BucketsToIssues'. Scan count 337, logical reads 1534, physical reads 0, read-ahead reads 0.
Table 'tbl_Categories'. Scan count 337, logical reads 674, physical reads 0, read-ahead reads 0.
Table 'tbl_Issues'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'tbl_ReportIssueDetails'. Scan count 1, logical reads 1965, physical reads 0, read-ahead reads 0.
Table 'tbl_ReportIssues'. Scan count 1, logical reads 746, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 8484 ms, elapsed time = 9075 ms.
SQL Server Execution Times:
CPU time = 8484 ms, elapsed time = 9091 ms.
SQL Server Execution Times:
CPU time = 8484 ms, elapsed time = 9092 ms.
SQL Server Execution Times:
CPU time = 8484 ms, elapsed time = 9092 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Running from SELECT statement
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(9 row(s) affected)
Table 'tbl_ReportIssueDetails'. Scan count 1, logical reads 1965, physical reads 0, read-ahead reads 0.
Table 'tbl_Issues'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'tbl_Categories'. Scan count 105, logical reads 210, physical reads 0, read-ahead reads 0.
Table 'tbl_EquipmentTypes'. Scan count 105, logical reads 226, physical reads 0, read-ahead reads 0.
Table 'tbl_EquipTypeToShow'. Scan count 1, logical reads 143, physical reads 0, read-ahead reads 0.
Table 'tbl_BucketsToIssues'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'tbl_ReportIssues'. Scan count 1, logical reads 730, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 85 ms, elapsed time = 85 ms.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 106 ms.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 106 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.