How to Improve Performance of Query in SQL Server

Question

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.

• Biju jose: can you use a temporary table on this? Instead of doing multiple IN statments I would suggest you to split the string and insert them into a temporary and join them with your main query.

Answer 1

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 are NOT 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 are null.

You can learn more about it here: Dynamic Search Conditions in T‐SQL.

• clifton_h: Or the OP could execute two different queries from store procedure. Few more lines of code, but worth avoiding headaches if this is massive.
• sepupic: @clifton_h OP has at least 7 parameters that can be 0 or NULL meaning that parameter should not be considered. How many different combinations of these can be? 128. So he should write out at least 128 queries?
• clifton_h: Look at the job what is doing. It is not really s query that should allow that many NULLs. Would you be ok if a unknown contractor worked in your house? Then why would he accept a NULL job requester? The answer is to think Qualify vs Hamming a flat solution. So the OP should consider forcing non-nulls where they make sense.
• sepupic: It looks as typical sp that SSRS uses. Any parameter can be omitted. So there can be 128 combinations of these parameters
• clifton_h: Typical != logical and typical !=efficient. I am saying there should be more consideration of what the OP is attempting from a Query perspective.

Answer 2

you can use Temp tables and No locks with the tables and can also use Option fast

SET NOCOUNT ON 
 DECLARE @vRequestedBy VARCHAR(2000) = CASE WHEN @RequestedBy <> '' THEN @RequestedBy END, 
 @vJobType NVARCHAR(2000) = CASE WHEN @JobType <> '' THEN @JobType END; 
 SELECT * INTO #Temp1 FROM (Select Email from dbo.wsm_Contact with (nolock) where Email in (select Item from Split_fn(@vRequestedBy,',')) ) T;
 SELECT * INTO #Temp2 FROM (SELECT t.FloorID FROM wsm_Contact_Tenancy t with (nolock) WHERE t.ContactID = @ContactUserId) t2;
 SELECT * INTO #Temp3 FROM (SELECT distinct b.BuildingId FROM wsm_ContactSite s with (nolock) INNER JOIN wsm_Contact c with (nolock) ON c.ContactID = s.ContactID INNER JOIN 
 wsm_Ref_Buildings b ON b.SiteId = s.SiteID 
 WHERE 
 c.ContactID = @ContactUserId) t3;
 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 with (nolock)
 LEFT OUTER JOIN 
 dbo.wsm_Ref_Buildings wb with (nolock) ON ts.BuildingID = wb.BuildingId 
 LEFT OUTER JOIN 
 dbo.wsm_Contact wc with (nolock) ON ts.TenancyID = wc.ContactID 
 LEFT OUTER JOIN 
 dbo.TSP_TSR_JobStatus TTJ with (nolock) ON ts.JobStatusID = TTJ.JobStatusID 
 LEFT OUTER JOIN 
 dbo.TSP_CAT_Category TCC with (nolock) ON ts.CategoryID = TCC.CategoryID 
 LEFT OUTER JOIN 
 dbo.wsm_Contact_User WCU with (nolock) ON UserID = ts.ContactEmail COLLATE SQL_Latin1_General_CP1_CI_AS 
 LEFT OUTER JOIN 
 dbo.wsm_Contact c2 with (nolock) 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 * from #Temp1 ))
 -- in (Select 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 * from #Temp3) 
 AND wc.FloorID IN (select * from #Temp2)
 --(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)) 
 -- group by JobID
 ORDER BY 
 JobID
 -- OPTION(RECOMPILE)
 option (fast 50)
 drop table #Temp1
 drop table #Temp2
 drop table #Temp3

Answer 3

Get rid of your Split_dn functions and use table valued parameters there. Then you ahve query statistics. Right now you have no statistics and thus seem to get a nice - totally non workign query plan.

And then - always try not to use distinct. It generally is not needed and makes thigns really slowish.

Once that is done, provide execution plans.

• Deepak Jain: what can be done instead of distinct ?
• TomTom: Forumlate different sql that does not need distinct.

Answer 4

Try to add # tables to your code and see if there is perforamnce benefit . ALso not there is a added overhead on this on temp db and memory but compare the performance and do the test.

The idea here is to load all corresponding ids to each temporary table and join them back to the main query. To generate a good plan thus avoding the scalar function from the select function.

CREATE PROCEDURE [dbo].[sp_SearchTSRsTenantByMultipleSel] 
 @JobId INT, 
 @CategoryId VARCHAR(2000), 
 @TenancyId VARCHAR(2000), 
 @TradingAs VARCHAR(2000), 
 @RequestedBy VARCHAR(2000), 
 @ContactUserID INT, 
 @JobType NVARCHAR(2000), 
 @CreatedFrom DATETIME, 
 @CreatedTo DATETIME 
AS 
BEGIN 
SET NOCOUNT ON 
 DECLARE @vRequestedBy VARCHAR(2000) = CASE 
 WHEN @RequestedBy <> '' 
 THEN @RequestedBy 
 END, 
 @vJobType NVARCHAR(2000) = CASE 
 WHEN @JobType <> '' 
 THEN @JobType 
 END; 
 CREATE TABLE #tenancyId
 (tenancyid int)
 CREATE TABLE #contactid
 (contactid int)
 CREATE TABLE #categoryid
 (categoryid int)
 CREATE TABLE #requestid
 (categoryid int)
 CREATE TABLE #buildingid
 (building int)
 CREATE TABLE #floorid
 (floorid int)
 CREATE TABLE #categoryid
 (categoryid int)
 CREATE TABLE #jobid
 (jobid int)
 INSERT INTO #tenancyId
 select Item from Split_fn(@TenancyID,',')
UNION 
SELECT 0 
INSERT INTO #contactid
(
 contactid
)
select Item from Split_fn(@TradingAs,',')
UNION 
SELECT 0
--populate the corresponding the #table like this.
 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, 
 t s.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 
 wsm_Contact c2 ON c2.ContactID = WCU.ContactID 
--JOIN /LEFT JOIN #tenancyId tid ON ts.tenancyid=tid.tenancyid
--Continue adding the join logic here for other table removing the where condition
--ALso don't forget to add indexes #table if needed
 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

• Deepak Jain: is there any way to remove the Man distinct (any alternative) to make the Query fast
• Biju jose: @nikhiljain without knowing how the data is I won't be able to do that. Also I'm not sure how I can remove distinct with other options.