SQL點滴之with語句和子查詢的性能比較

在博友SingleCat的提醒下，對with語句做一些性能測試，這裡使用的測試工具是SQL Server Profile。我選擇了最後一個語句，

因為這個語句比較復雜一點。開始的時候單獨執行一次發現他們的差別不大，就差幾個毫秒，後來想讓他們多執行幾次，連續執行10

次看看執行的結果。下面貼出測試用的語句。

/*with查詢*/  
declare @withquery varchar(5000)  
declare @execcount int=0  
set @withquery='with TheseEmployees as(  
select empid from hr.employees where country=N''USA''),  
CharacteristicFunctions as(  
select custid,  
       case when custid in (select custid from sales.orders as o where o.empid=e.empid)
 then 1 else 0 end as charfun  
from sales.customers as c cross join TheseEmployees as e)  
select custid from CharacteristicFunctions group by custid having min(charfun)=1
 order by custid  
'  
while @execcount<10  
begin 
exec (@withquery);  
set @execcount=@execcount+1  
end 
 
/*子查詢*/  
declare @subquery varchar(5000)  
declare @execcount int=0  
set @subquery='select custid from Sales.Orders where empid in 
(select empid from HR.Employees where country = N''USA'') group by custid  
having count(distinct empid)=(select count(*) from HR.Employees where country = N''USA'');  
'  
while @execcount<10  
begin 
exec (@subquery);  
set @execcount=@execcount+1  
end 

從SQL Server Profile中截圖如下

從圖中可以看到子查詢語句的執行時間要少於with語句，我覺得主要是with查詢中有一個cross join做了笛卡爾積的關系，

於是又實驗了上面的那個簡單一點的，下面是測試語句。

/*with語句*/  
declare @withquery varchar(5000)  
declare @execcount int=0  
set @withquery='with c(orderyear,custid) as(  
select YEAR(orderdate),custid from sales.orders)  
select orderyear,COUNT(distinct(custid)) numCusts from c group by c.orderyear'   
while @execcount<100  
begin 
exec (@withquery);  
set @execcount=@execcount+1  
end 
 
/*子查詢*/  
declare @subquery varchar(5000)  
declare @execcount int=0  
set @subquery='select orderyear,COUNT(distinct(custid)) numCusts  
from (select YEAR(orderdate),custid from sales.orders) as D(orderyear,custid)  
group by orderyear'  
while @execcount<100  
begin 
exec (@subquery);  
set @execcount=@execcount+1  
end 

這次做10次查詢還是沒有多大的差距，with語句用10個duration，子查詢用了11個，有時候還會翻過來。

於是把執行次數改成100，這次還是子查詢使用的時間要少，截圖如下

最終結論，子查詢好比with語句效率高。