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

之前筆者和大家分享了《使用with語句來寫一個稍微複雜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語句效率高。