how to Improve Performance of Distinct Query In Sql server

Admin 1
Viewed 1358

i have a Query and Currently it's taking 10 sec for 324 Records only is there any way to Improve this performance.Ps i am very new to Sql server.

Tried: have used SET NOCOUNT ON in the SP as i have read that it Improves Performance and Even have used alias for each table.please let me know what can be done to Improve it's performace

DECLARE @vRequestedBy VARCHAR(2000) = CASE WHEN @RequestedBy <> '' THEN @RequestedBy END,  
   @vJobType NVARCHAR(2000) = CASE WHEN @JobType <> '' THEN @JobType END;  
SELECT  distinct ts.JobID,   
  dbo.TSP_CAT_Category.Category,   
  ts.JobType,   
  dbo.TSP_TSR_JobStatus.JobStatus,   
  dbo.wsm_Contact.Name "ContactName",   
  ts.Created,   
  wb.Name AS BuildingName,   
  ts.Contact,   
 ts.CreatedBy,   
  ts.ContactEmail,   
  dbo.wsm_Contact.TradingAs,  
  --wsm_Contact_User.UserId "RequestedByUserId",  
  c2.Name "RequestedByUser",  
  dbo.wsm_Contact.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 ON ts.TenancyID = dbo.wsm_Contact.ContactID   
  LEFT OUTER JOIN   
   dbo.TSP_TSR_JobStatus ON ts.JobStatusID = dbo.TSP_TSR_JobStatus.JobStatusID   
  LEFT OUTER JOIN   
   dbo.TSP_CAT_Category ON ts.CategoryID = dbo.TSP_CAT_Category.CategoryID  
  LEFT OUTER JOIN   
   dbo.wsm_Contact_User ON UserID = ts.ContactEmail COLLATE SQL_Latin1_General_CP1_CI_AS  
  LEFT OUTER JOIN   
   wsm_Contact c2 ON c2.ContactID = wsm_Contact_User.ContactID  

  WHERE     
   -- JobId criteria  
    (@JobID = 0 OR JobID = @JobId)  

   AND (@TenancyId = '0' OR TenancyId in (select Item from Split_fn(@TenancyID,',')))  

   AND (@TradingAs = '0' OR wsm_Contact.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 wsm_Contact.FloorID  IN     (SELECT t.FloorID   
              FROM wsm_Contact_Tenancy t  
             WHERE t.ContactID = @ContactUserId)  

   AND wsm_Contact.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 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.
query-performance sql-server
3 Answers
  1. Add clustered index for wsm_Ref_Buildings
  2. Turn each IN into EXISTS
  3. Add OPTION RECOMPILE since you really need all those ORs
  4. Replace Split_fn body with something from internet (there are more clean examples of such code as well as shipped with SQL SERVER SPLIT_STRING function), make sure Split_fn is DETERMINISTIC (requires WITH SCHEMABINDING option)
  5. Try to get rid of all the DISTINCTs
  6. Fix all the warnings (at least any of them) about indexes and missing statistics (like for TSP_CAT_Category table)
  7. Paste new ACTUAL execution plan (not estimated one)
  8. Maybe someday convert all your PKs from strings to integers

DISTINCTs within your IN subqueries do nothing but senseless extra sorting which has no effect on query logic or output. The very top DISTINCT fixes (or still does nothing) badly designed query: duplicate rows (if any) are produced by wrongly defined join which must be fixed (e.g. OUTER APPLY (SELECT TOP 1...)).

upd

Exists example:

WHERE ts.CategoryID in (select Item from Split_fn(@CategoryId,',') )

-->>

WHERE EXISTS (select 1 from Split_fn(@CategoryId,',') s WHERE s.Item = ts.CategoryID)
user_59218260

There is an good set of suggestions by Ivan Starostin which I won't repeat here, but I would ask you to consider why you feel the need to use "distinct" in the first place.

select distinct ADDS time and effort to queries, it isn't a good approach to "wide queries" (queries with many column) particularly if the query involves several joined tables. While joining tables together is commonplace and necessary, don't forget joins often have the effect of MULTIPLYING the number of rows. So, if there are too many rows re-consider the joins before relying purely on select distinct as the panacea.

For example, does this require "distinct"?

SELECT
    ts.JobID
  , ts.Contact
  , ts.ContactEmail
  , ts.Created
  , ts.CreatedBy
  , ts.JobType
FROM dbo.TSP_TSR_Job ts
WHERE (ts.Created BETWEEN @CreatedFrom AND DATEADD(DD, 1, @CreatedTo))
AND (@JobID = 0 OR ts.JobID = @JobId)
AND (@TenancyId = '0' OR ts.TenancyId IN (
    SELECT
        Item
    FROM Split_fn(@TenancyID, ',')
    )
)

If (as I suspect) that doesn't need "distinct" then use this as a subquery, and add the remaining tables afterwards. You might also be able to incorporate the lookup tables for category and status without adding further rows, e.g.

SELECT
    ts.JobID
  , ts.Contact
  , ts.ContactEmail
  , ts.Created
  , ts.CreatedBy
  , ts.JobType
  , dbo.TSP_CAT_Category.Category
  , dbo.TSP_TSR_JobStatus.JobStatus
FROM dbo.TSP_TSR_Job ts
LEFT OUTER JOIN dbo.TSP_TSR_JobStatus    ON ts.JobStatusID = dbo.TSP_TSR_JobStatus.JobStatusID
LEFT OUTER JOIN dbo.TSP_CAT_Category     ON ts.CategoryID = dbo.TSP_CAT_Category.CategoryID
WHERE (ts.Created BETWEEN @CreatedFrom AND DATEADD(DD, 1, @CreatedTo))
AND (@JobID = 0 OR ts.JobID = @JobId)
AND (@TenancyId = '0' OR ts.TenancyId IN (
    SELECT
        Item
    FROM Split_fn(@TenancyID, ',')
    )
)
-- Job Category  
AND (@CategoryId = '0'
OR ts.CategoryID IN (
    SELECT
        Item
    FROM Split_fn(@CategoryId, ',')
)
)
AND (@vJobType IS NULL
OR ts.JobType IN (
    SELECT
        Item
    FROM Split_fn(@vJobType, ',')
)
)

If that doesn't need "distinct" then us it as a subquery ("derived table" or "common table expression") and then try adding each additional join one by one (i.e. add the join, and the relevant where clause filter and add the selected columns for this table too). Then if you start to see unwanted repetition in the results after that extra join, you know where the repetition comes from. You may need quite a different approach to that table to solve this (e.g. joining a subquery that uses row_number() to get only the "most recent" contact).

user_20677530

you can make the alias for wsm_Contact , TSP_TSR_JobStatus ,TSP_CAT_Category , wsm_Contact_User and user their alises as well in the query

user_112301920
Related