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