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语句效率高。

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