sql - 如何提高 SQL Server 中以下查询的性能？

Question

执行计划显示以下查询的 3 条警告消息：

set statistics io on;
select DISTINCT r.CASE_ID as RequisitionId,
e.BV_Employee_Code as EmployeeCode,e.CASE_ID as CaseID,
e.BV_First_Name as FirstName,e.BV_Last_Name as LastName,
e.BV_Email as Email,e.BV_Status as 'Status', e.BV_hrmsFlag as 'hrmsFlag',
l.BV_Country as 'Country',l.BV_LocationCode,l.BV_LocationName,
DV.BV_Division_Code as Division_Code ,DV.BV_Division_Name, 
AQ.BV_hiringreason,
CASE WHEN BV_Offer_Annual_Rate IS NULL AND BV_OFFER IS NULL AND BV_OFFER_TITLE IS NULL AND BV_Approved_By IS NULL THEN 'Y' ELSE 'N' END ISMANUALHIRED, e.BV_SSN as SSN 
from CW_V_REQN r 
inner join CW_TL_Employee__Requisition er on r.CASE_ID=er.TO_ID 
inner join CW_V_EMPLOYEE e on e.CASE_ID =er.FROM_ID 
inner join CW_TL_Requisition__Location_Master rl on r.CASE_ID=rl.FROM_ID 
inner join CW_V_LOCTMAST l on l.CASE_ID=rl.TO_ID 
inner join CW_V_LOCTMAST l1 ON (l1.CASE_ID = rl.TO_ID) 
inner join CW_TL_LocationMaster__Division_Master ld ON (ld.FROM_ID = rl.TO_ID) 
inner join CW_V_DIVISION dv ON (dv.CASE_ID = ld.TO_ID) 
inner join CW_TL_UserContactInfo__Location_Master ul on l.CASE_ID=ul.TO_ID 
inner join CW_V_USERCONT uc on uc.CASE_ID=ul.FROM_ID 
inner join CW_TL_Employee__Applicant_Requisition_Details ea ON (ea.FROM_ID = er.FROM_ID) 
inner join CW_V_AREQDETA aq ON (aq.CASE_ID = ea.TO_ID)
left join CW_TL_Employee__Employee_Job_Offer ee ON (EE.FROM_ID = er.FROM_ID) 
left join CW_V_EMPJOBOF ej ON (ej.CASE_ID = ee.TO_ID AND ej.BV_REQUISITIONID = r.CASE_ID) 
where e.BV_Status  in('Pending','Employed','LOA','Suspended','Terminated','Pre-Screening') 
and uc.BV_Login_Name='vpawar'
ORDER BY r.CASE_ID ASC

统计如下：

(995 row(s) affected)
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 29, logical reads 442, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_AREQDETA'. Scan count 0, logical reads 6115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_71'. Scan count 999, logical reads 3002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_EMPLOYEE'. Scan count 0, logical reads 5205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_59'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_LOCTMAST'. Scan count 1, logical reads 16791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_130'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_CT_USERCONT'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_USERCONT'. Scan count 1, logical reads 81, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_30'. Scan count 1, logical reads 44, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_REQN'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_65'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_DIVISION'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TL_24'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CW_TB_EMPJOBOF'. Scan count 1, logical reads 145, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

三个排序运算符告诉我们：

操作员在执行期间使用 tempdb 以溢出级别 1 溢出数据

我认为 select 子句有问题，如果我不修剪任何列，那些警告就消失了。我已尝试添加新索引或修改现有索引以包含覆盖列。如果我的选择子句仅限于表 CW_V_EMPLOYEE，那么这些警告就会消失，并且建议我添加我添加但没有任何效果的缺失索引。

编辑：我重写了查询如下：

set statistics time on; 
set statistics io on;
with LocDivUserCont_CTE as
(
select r.CASE_ID,loc.BV_Country as Country, loc.BV_LocationCode, 
loc.BV_LocationName,
dv.BV_Division_Code as Division_Code,dv.BV_Division_Name
from CW_V_REQN as r
inner join CW_TL_Requisition__Location_Master rl on r.CASE_ID=rl.FROM_ID 
inner join CW_V_LOCTMAST loc on loc.CASE_ID=rl.TO_ID 
inner join CW_TL_LocationMaster__Division_Master ld ON (ld.FROM_ID = 
rl.TO_ID) 
inner join CW_V_DIVISION dv ON (dv.CASE_ID = ld.TO_ID) 
inner join CW_TL_UserContactInfo__Location_Master ul on loc.CASE_ID=ul.TO_ID 
inner join CW_V_USERCONT uc on uc.CASE_ID=ul.FROM_ID 
where  uc.BV_Login_Name='vpawar'
)

select distinct  r.CASE_ID as RequisitionId,
e.BV_Employee_Code as EmployeeCode,e.CASE_ID as CaseID,
e.BV_First_Name as FirstName,e.BV_Last_Name as LastName,
e.BV_Email as Email,e.BV_Status as 'Status', e.BV_hrmsFlag as 'hrmsFlag',e.BV_SSN as SSN,
aq.BV_hiringreason,
CASE WHEN BV_Offer_Annual_Rate IS NULL AND BV_OFFER IS NULL AND BV_OFFER_TITLE IS NULL AND BV_Approved_By IS NULL THEN 'Y' ELSE 'N' END ISMANUALHIRED
from CW_V_REQN r 
inner join CW_TL_Employee__Requisition er on r.CASE_ID = er.TO_ID 
inner join CW_V_EMPLOYEE e on e.CASE_ID = er.FROM_ID 
inner join CW_TL_Employee__Applicant_Requisition_Details ea ON (ea.FROM_ID = er.FROM_ID) 
inner join CW_V_AREQDETA aq ON (aq.CASE_ID = ea.TO_ID)
inner join LocDivUserCont_CTE on r.CASE_ID = LocDivUserCont_CTE.CASE_ID
left join CW_TL_Employee__Employee_Job_Offer ee ON (EE.FROM_ID = er.FROM_ID) 
left join CW_V_EMPJOBOF ej ON (ej.CASE_ID = ee.TO_ID AND ej.BV_REQUISITIONID = r.CASE_ID) 
where e.BV_Status  in('Pending','Employed','LOA','Suspended','Terminated','Pre-Screening') 
order by r.CASE_ID desc;
set statistics io off;
set statistics time off;

有了这个，“排序”运算符中的所有警告都消失了。这里是根据早期查询和当前查询的扫描和逻辑读取数据列表。它也需要更少的时间。您会看到表 CW_TB_LOCTMAST 的逻辑读取从 16779 急剧减少到 15，但表 CW_TB_REQN 的逻辑读取从 40 增加到 80。

表 |扫描前 |逻辑读取前 |扫描后 |逻辑读取后
工作文件 | 0 |0 |0 |0
工作台 |29 |442 |0 |0
CW_TB_AREQDETA |0 |6115 |0 |6115
CW_TL_71 |999 |3002 |1 |15
CW_TB_EMPLOYEE |0 |5205 |0 |5205
CW_TL_59 |4060 |8668 |1 |15
CW_TB_LOCTMAST |1 |16779 |1 |15
CW_TL_130 |1 |21 |1 |21
CW_CT_USERCONT |1 |2 |1 |2
CW_TB_USERCONT |1 |81 |1 |81
CW_TL_30 |1 |44 |1 |44
CW_TB_REQN |1 |40 |2 |80
CW_TL_65 |1 |14 |1 |14
CW_TB_DIVISION |1 |6 |1 |2
CW_TL_24 |1 |14 |1 |14
CW_TB_EMPJOBOF |1 |145 |1 |145

但是，我应该使用第二个查询，我错过了什么吗？

标签： sqlsql-serversql-tuning