Performance Comparison Between with statements and subqueries

Previously, I shared with you "using with statements to write a slightly complex SQL statement". This time I made a Performance Comparison Between with statements and subqueries.

As instructed by Boyou SingleCat, perform some performance tests on the with statement. The test tool used here is SQL Server Profile. I chose the last statement because it is more complex. At the beginning, they were executed only once and found that the difference was not big, just a few milliseconds. Later, they wanted to execute several more times and execute 10 consecutive times.

Check the execution result. The following is a test statement.

 
 

  
  
/* With query */
  
  
Declare @ withquery varchar (5000)
  
  
Declare @ execcount int = 0
  
  
Set @ withquery = 'with TheseEmployees (
  
  
Select empid from hr. employees where country = n''usa ''),
  
  
CharacteristicFunctions (
  
  
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
  
  
 
  
  
/* Subquery */
  
  
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
 
 

The SQL Server Profile is as follows:

We can see that the execution time of the subquery statement is less than that of the with statement. I think there is a cross join in the with query that has a Cartesian Product relationship, so I tried the simple one above. below is the test statement.

 
 

  
  
/* With statement */
  
  
Declare @ withquery varchar (5000)
  
  
Declare @ execcount int = 0
  
  
Set @ withquery = 'with c (orderyear, custid) (
  
  
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
  
  
 
  
  
/* Subquery */
  
  
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
 
 

There is still no big gap in this 10 queries. The with statement uses 10 durations and 11 subqueries, sometimes turning over. Therefore, the number of executions is changed to 100. This time the subquery takes less time, as shown below:

The final conclusion is that subqueries are more efficient than with statements.