之前筆者和大家分享了《使用with語句來寫一個(gè)稍微復(fù)雜sql語句》，這一次筆者針對with語句和子查詢做了一個(gè)性能的比較。

在博友SingleCat的提醒下，對with語句做一些性能測試，這里使用的測試工具是SQL Server Profile。我選擇了***一個(gè)語句，因?yàn)檫@個(gè)語句比較復(fù)雜一點(diǎn)。開始的時(shí)候單獨(dú)執(zhí)行一次發(fā)現(xiàn)他們的差別不大，就差幾個(gè)毫秒，后來想讓他們多執(zhí)行幾次，連續(xù)執(zhí)行10

次看看執(zhí)行的結(jié)果。下面貼出測試用的語句。

/*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中截圖如下

從圖中可以看到子查詢語句的執(zhí)行時(shí)間要少于with語句，我覺得主要是with查詢中有一個(gè)cross join做了笛卡爾積的關(guān)系，于是又實(shí)驗(yàn)了上面的那個(gè)簡單一點(diǎn)的，下面是測試語句。

/*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個(gè)duration，子查詢用了11個(gè)，有時(shí)候還會(huì)翻過來。于是把執(zhí)行次數(shù)改成100，這次還是子查詢使用的時(shí)間要少，截圖如下

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

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/04/18/2020225.html

【編輯推薦】

1. SQL點(diǎn)滴之使用attach功能出現(xiàn)錯(cuò)誤及解決方法
2. SQL點(diǎn)滴之一個(gè)簡單的字符串分割函數(shù)
3. SQL點(diǎn)滴之重置win7登錄密碼對SQL登錄的影響
4. SQL點(diǎn)滴之SSIS中的事務(wù)處理
5. SQL點(diǎn)滴之使用with語句來寫一個(gè)稍微復(fù)雜sql語句