CTEs may not be used for performance but there are times they could help you from a performance standpoint. The following example uses the same logic written in a way to use three different types of temporary objects. It shows what is done with each process using Client Statistics and their impact on operations.
#Temp Tables:
Looking at the query below you can see that the logic is very basic. The result set was also very low for this as well so I added 100 after “GO” for the three processes to have the query repeat 100 times. While it does not offer a load, it adds to the session to make it use a little more resource.
With #temp tables there are some things that I would like to point out:
Query Profile Statistics:
- There was a total number of 200 INSERT, DELETE, and UPDATE statements
- 1,700 rows affected by INSERT, DELETE, or UPDATE statements
- 401 SELECT statements
- 1901 rows returned by SELECT statements
- 200 transactions
Time Statistics:
- Client processing time = 803
- Total execution time = 1292
- Wait time = 489
To add all of that by using a #temp table it was able to process the same logic 100 times, returning 1700 records at 1:02.
@Table Variables:
Looking at the logic below you can see that it is doing the same thing as the previous query but using a @Table Variable instead of a #temp table.
With @Table Variables I would like to point out the following:
Query Profile Statistics:
- There was a total number of 200 INSERT, DELETE, and UPDATE statements
- 1,700 rows affected by INSERT, DELETE, or UPDATE statements
- 401 SELECT statements
- 1901 rows returned by SELECT statements
- 200 transactions
Time Statistics:
- Client processing time = 1126
- Total execution time = 1423
- Wait time = 297
By adding all of that @Table Variables returned 1700 rows in only 58 seconds. That was a 4-second improvement.
While comparing #Temp Tables and @Table Variables you can see that the Query Profile Statistics are the same but the Time Statistics are different. Remember that both #Temp Tables and @Table Variables are created as objects and stored in TempDB. Since the logic is doing the same thing the same amount of records are being processed by both but they are executed differently which is why the query length is close but still different.
CTEs:
Out of the three, you can see that the logic is similar and will give you the same results however CTEs are different than the other two.
CTEs do not put objects into TempDB. CTEs are executed at the time the query is run as any other subquery would run. So out of the three, you should see the biggest difference with CTEs pertaining to the Client Statistics.
With CTEs I would like to point out the following:
Query Profile Statistics:
- There was a total number of 0 INSERT, DELETE, and UPDATE statements
- 0 rows affected by INSERT, DELETE, or UPDATE statements
- 301 SELECT statements
- 1801 rows returned by SELECT statements
- 0 transactions
Time Statistics:
- Client processing time = 799
- Total execution time = 946
- Wait time = 147
The CTE query also returned 1700 records at 28 seconds which has cut the time down by half. While 28 seconds can still be considered a very long time to run, it has made a huge difference between the other two options.
By looking at the Query Profile Statistics you can see why. There are 0s for total number and rows affected by an INSERT, DELETE, and UPDATE as well as 0 transactions. The point here is that CTE can require less work which means that you may be able to get more out of them than some of the other options. The more work the SQL engine needs to do the more resources and time that it takes to run. While this will not work for everything, CTEs are a valid option to help your developing needs when applicable.
