之前笔者和大家分享了《使用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