I have a query and currently it's taking 10 seconds for only 324 records. Is there any way to improve this performance?
I have tried using SET NOCOUNT ON
in the SP as I have read that it improves performance and even have used an alias for each table.
Execution Plan (Paste The Plan)
SET NOCOUNT ON
DECLARE @vRequestedBy VARCHAR(2000) = CASE WHEN @RequestedBy <> '' THEN @RequestedBy END,
@vJobType NVARCHAR(2000) = CASE WHEN @JobType <> '' THEN @JobType END;
SELECT distinct ts.JobID,
TCC.Category,
ts.JobType,
ttj.JobStatus,
wc.Name "ContactName",
ts.Created,
wb.Name AS BuildingName,
--dbo.TSP_TSR_Job.JobStatusID,
--dbo.TSP_TSR_Job.BuildingID,
--dbo.TSP_TSR_Job.TenancyID,
--dbo.TSP_TSR_Job.CategoryID,
ts.Contact,
ts.CreatedBy,
ts.ContactEmail,
wc.TradingAs,
--wsm_Contact_User.UserId "RequestedByUserId",
c2.Name "RequestedByUser",
wc.ContactID
FROM
dbo.TSP_TSR_Job ts
LEFT OUTER JOIN
dbo.wsm_Ref_Buildings wb ON ts.BuildingID = wb.BuildingId
LEFT OUTER JOIN
dbo.wsm_Contact wc ON ts.TenancyID = wc.ContactID
LEFT OUTER JOIN
dbo.TSP_TSR_JobStatus TTJ ON ts.JobStatusID = TTJ.JobStatusID
LEFT OUTER JOIN
dbo.TSP_CAT_Category TCC ON ts.CategoryID = TCC.CategoryID
LEFT OUTER JOIN
dbo.wsm_Contact_User WCU ON UserID = ts.ContactEmail COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN
dbo.wsm_Contact c2 ON c2.ContactID = WCU.ContactID
WHERE
-- JobId criteria
(@JobID = 0 OR JobID = @JobId)
-- Tenancy criteria
AND (@TenancyId = '0' OR TenancyId in (select Item from Split_fn(@TenancyID,',')))
--TradingAs criteria
AND (@TradingAs = '0' OR wc.ContactID in (select Item from Split_fn(@TradingAs,',') ))
--RequestedBy
AND (@vRequestedBy IS NULL OR @vRequestedBy = '0' OR ts.ContactEmail in (Select distinct Email from dbo.wsm_Contact WHere Email in (select Item from Split_fn(@vRequestedBy,',')) ))
-- Job Category
AND (@CategoryId = '0' OR ts.CategoryID in (select Item from Split_fn(@CategoryId,',') ))
-- Contact Id (always filter on this, enough security?!)
AND ts.BuildingID IN (SELECT distinct b.BuildingId
FROM
wsm_ContactSite s
INNER JOIN
wsm_Contact c ON c.ContactID = s.ContactID
INNER JOIN
wsm_Ref_Buildings b ON b.SiteId = s.SiteID
WHERE
c.ContactID = @ContactUserId)
AND wc.FloorID IN (SELECT t.FloorID
FROM wsm_Contact_Tenancy t
WHERE t.ContactID = @ContactUserId)
AND wc.OCCPSTAT NOT IN ('I', 'P')
AND (@vJobType IS NULL OR ts.JobType in (select Item from Split_fn(@vJobType,',')))
AND (ts.Created between @CreatedFrom and DATEADD(DD,1,@CreatedTo))
ORDER BY
JobID
STATS:
SQL Server parse SQL S 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.
Table 'TSP_CAT_Category'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'wsm_Contact_Tenancy'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'wsm_Contact'. Scan count 2, logical reads 3822, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'wsm_ContactSite'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'wsm_Ref_Buildings'. Scan count 3, logical reads 2811, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 341364, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#AFEC4F2F'. Scan count 2, logical reads 524444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TSP_TSR_Job'. Scan count 3, logical reads 58210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'wsm_Contact_User'. Scan count 2, logical reads 2300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TSP_TSR_JobStatus'. Scan count 2, logical reads 650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '1159564537'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BB5E01DB'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BA69DDA2'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B1D497A1'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B0E07368'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 8391 ms, elapsed time = 5792 ms.
SQL Server Execution Times:
CPU time = 8391 ms, elapsed time = 5793 ms.
Best Answer
You can add
OPTION (RECOMPILE)
at the end of your query if you can permit recompilations, or you can rewrite your query constructing it dynamically by analyzing your search conditions and adding them to your query only if they areNOT NULL
.Your sp now depends on the first input parameters that were sniffed, i.e. the plan that is used was built based on the parameters passed to this sp at the first execution, i.e. it's not optimal if the first time some parameters passed were null and now they are
not null
, or other parameter arenull
.You can learn more about it here: Dynamic Search Conditions in T‑SQL.