sql-server - 如何提高Sql server中不同查询的性能

Question

我有一个查询，目前仅需要 10 秒即可处理 324 条记录，有什么方法可以提高此性能。Ps 我对 Sql 服务器非常陌生。

尝试过:在 SP 中使用了SET NOCOUNT ON，因为我读到它提高了性能，甚至为每个表使用了alias。请让我知道可以采取哪些措施来改进它性能

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

统计:

 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.

Answer 1

1. 为wsm_Ref_Buildings添加聚集索引
2. 将每个 IN 转换为 EXISTS
3. 添加OPTION RECOMPILE，因为您确实需要所有这些OR
4. 将 Split_fn 主体替换为互联网上的内容(此类代码有更干净示例，并且随 SQL SERVER SPLIT_STRING 函数一起提供)，确保 Split_fn 为 DETERMINISTIC(需要 WITH SCHEMABINDING 选项)
5. 尝试去掉所有的DISTINCT
6. 修复有关索引和缺失统计信息(例如 TSP_CAT_Category 表)的所有警告(至少其中任何警告)
7. 粘贴新的实际执行计划(不是估计计划)
8. 也许有一天将您所有的 PK 从字符串转换为整数

IN 子查询中的

DISTINCT 除了无意义的额外排序之外什么也不做，这对查询逻辑或输出没有影响。最上面的 DISTINCT 修复(或仍然不执行任何操作)设计不良的查询:重复的行(如果有)是由错误定义的联接产生的，必须修复(例如 OUTER APPLY (SELECT TOP 1. ..)).

更新

存在示例:

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

-->>

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