Table Variable VS. Temp Table - Execution Plan Differences Explained in Details - Part 2

In the previous post

Table Variable VS. Temp Table - Execution Plan Differences Explained in Details - Part 1

 we've discussed the execution plan of joining a table variable. In this article, we'll keep analysing the execution plan of joining a temp table, as well as some variations which might help us improve the performance of similar T-SQL scripts in our work.

If you recall the screenshots we put in the previous post, Hash Match (HM) is used when joining temp table with other tables. If we hover over the first Hash Match from the right in the execution plan window, we get the below information


It has explained it self pretty well - each row in the temp table #CTE is used to build a hash table, and then each row in the table Person.BusinessEntity is used to probe into the hash table, according to the values of the column BusinessEntityID in both tables, and then output all matching rows. Because each row in the table Person.BusinessEntity is used for probe, a Clustered Index Scan is conducted.

If you've read relative articles or books about performance tuning, then you'll know that Nested Loop is extremely efficient when the Outer Input (in our example it's @CTE) has a small amount of records, whereas Hash Match works better for large, unsorted records. This probably explains why some specialists claims that table variable is better for holding small amount of data, whereas temp table the opposite. It also tells us that maybe sometimes SQL Server Optimiser is not that smart and efficient, otherwise in our examples it would've used Nested Loop in both cases. Of course in our examples it doesn't matter, but what if it does in some occasions? Well that way we could use query hints to help optimiser to choose the wiser solution.

If we know that we're dealing with small amount of data, we could use the hint OPTION (LOOP JOIN) to force optimiser to choose Nested Loop join instead of Hash Match. Let's change our script as below

SELECT
       BEA.BusinessEntityID
       ,BEA.AddressID
       ,BEA.AddressTypeID
       ,ADT.Name
INTO #CTE
FROM [Person].[BusinessEntityAddress] BEA
INNER JOIN [Person].[AddressType] ADT ON BEA.AddressTypeID = ADT.AddressTypeID

SELECT
       CS.BusinessEntityID
       ,CS.AddressID
       ,CS.AddressTypeID
       ,BE.ModifiedDate
       ,AD.AddressLine1
       ,AD.AddressLine2
       ,AD.City
       ,Ad.PostalCode
       ,AD.StateProvinceID
       ,CS.Name
FROM #CTE CS
INNER JOIn [Person].[BusinessEntity] BE ON CS.BusinessEntityID = BE.BusinessEntityID
INNER JOIN [Person].[Address] AD ON CS.AddressID = AD.AddressID
OPTION (LOOP JOIN)

and the execution plan



Because we've forced Optimiser to use Nested Loop, the execution plan is almost the same as that of joining table variable, except that an extra Parallelism is added. That's because the two Nested Loops are being processed simultaneously which generates two streams, and the two streams are gathered together. If we add another hint of MAXDOP 1 as below

SELECT
       BEA.BusinessEntityID
       ,BEA.AddressID
       ,BEA.AddressTypeID
       ,ADT.Name
INTO #CTE
FROM [Person].[BusinessEntityAddress] BEA
INNER JOIN [Person].[AddressType] ADT ON BEA.AddressTypeID = ADT.AddressTypeID

SELECT
       CS.BusinessEntityID
       ,CS.AddressID
       ,CS.AddressTypeID
       ,BE.ModifiedDate
       ,AD.AddressLine1
       ,AD.AddressLine2
       ,AD.City
       ,Ad.PostalCode
       ,AD.StateProvinceID
       ,CS.Name
FROM #CTE CS
INNER JOIn [Person].[BusinessEntity] BE ON CS.BusinessEntityID = BE.BusinessEntityID
INNER JOIN [Person].[Address] AD ON CS.AddressID = AD.AddressID

OPTION (LOOP JOIN, MAXDOP 1)

and the execution plan

Wow, now it's the same as the one generated by that of joining a table variable. Yeah!

Our journey is not finished. We know that in our example the amount of data is quite small, so to be honest each plan is acceptable. What if we have to deal with large amount of data? Theoretically, table scan on a big table is expensive. Let's give Optimiser another hand by changing our script as below

SELECT
       BEA.BusinessEntityID
       ,BEA.AddressID
       ,BEA.AddressTypeID
       ,ADT.Name
INTO #CTE
FROM [Person].[BusinessEntityAddress] BEA
INNER JOIN [Person].[AddressType] ADT ON BEA.AddressTypeID = ADT.AddressTypeID

CREATE NONCLUSTERED INDEX IX_CTE_BusinessEntityID
ON #CTE (BusinessEntityID)
INCLUDE (AddressID,AddressTypeID,Name)

SELECT
       CS.BusinessEntityID
       ,CS.AddressID
       ,CS.AddressTypeID
       ,BE.ModifiedDate
       ,AD.AddressLine1
       ,AD.AddressLine2
       ,AD.City
       ,Ad.PostalCode
       ,AD.StateProvinceID
       ,CS.Name
FROM #CTE CS
INNER JOIn [Person].[BusinessEntity] BE ON CS.BusinessEntityID = BE.BusinessEntityID

INNER JOIN [Person].[Address] AD ON CS.AddressID = AD.AddressID

and the execution plan

In the above script, we created a non-clustered index on the column BusinessEntityID in the temp table #CTE, and included the rest of the columns. We can see it very clearly that the below things are happening in the execution plan:
  • Because our temp table is indexed, a Merge Join is conducted. Merge join is more efficient than Hash Match when both input tables are sorted (which is true in our example as both #CTE and Person.BusinessEntity are indexed). The Merge Join scans both indexes and 'merges' them together, so as to get the final output. Please search online or read books about performance tuning if you want to understand further about the algorithms regarding Merge Join, Nested Loop and Hash Match.
  • The above output is then used to build a hash table, and then each row in table Person.Address is used for probe, using column AddressID in both the hash table and in Person.Address, and when matched, output the records.
I hope you've enjoyed the series of discussing differences between CTE, table variable and temp table. Still, performance tuning is a trial and error process and it's hard to find the best solution sometimes. Understanding how Optimiser works can help us a lot at least finding an ideal solution.

Comments

Post a Comment

Popular posts from this blog

SQL Server - Using SQL Agent JOBs to Schedule and Execute Multiple Stored Procedures in Parallel

This article introduces a way to schedule and kick off multiple stored procedures in parallel. Suppose we need to build an ETL system to migrate data from the source system to the target in T-SQL script; we've created multiple stored procedures (SP) each of which contains the payload of extracting data from one module of the source system and loading it to the target system; any of those stored procedures can be executed independently without relying on the execution statuses of any other stored procedures; and to speed up the whole process, we need to run multiple stored procedures simultaneously. Ideally, the above task can be handled easily in SSIS. Of course we could've created a Sequence Container and dropped a batch of SP execution tasks to it in SSIS, and then another Sequence Container containing the next batch of SP execution tasks, etc., but what if using SSIS has become a luxury for us (for example, your boss is not a fan of SSIS and prohibits you from using it as ...
Read more

sp_executesql – Let's learn How to Use It

Image
In the post SQL Server - Using SQL Agent JOBs to Scheduleand Execute Multiple Stored Procedures in Parallel , I’ve demonstrated the usage of sp_executesql and how powerful and helpful it is. This time I’ll put more explanations about it. Talk is cheap, and let’s see the code. DECLARE @ModDate datetime = '2017-12-04 00:00:00.000' SELECT        [AddressID]     , [AddressLine1]     , [AddressLine2]     , [City]     , [StateProvinceID]     , [PostalCode]     , [SpatialLocation]     , [rowguid]     , [ModifiedDate] FROM [AdventureWorks2017] . [Person] . [Address] WHERE [ModifiedDate] <= @ModDate The above code is straightforward enough. Now let’s consider the below scenarios – suppose our database name is composed by the keyword ‘AdventureWorks’ plus the year number suffix (in our example it’s 2017); ...
Read more

Installing Azure PowerShell Module in Your Local Windows PowerShell

Image
The Azure PowerShell Module can be used to automate the creation, management and deployment of resources in Microsoft Azure Portal, so it's very useful and we'd better have it installed in our local Windows PowerShell. We can simply follow the below steps to achieve this. I. Firstly launch Windows Powershell with an administrator’s credential and execute the $PSVersionTable.PSVersion command to check the current version of PowerShell that’s been installed. As per the below screenshot, the version installed in my machine is 5.1. This is the minimum version that’s required to install the Azure PowerShell module. II. Install .NET Framework 4.8 in your Windows system. In my case the install program indicates me that it’s already been installed.  III. In PowerShell run the Update-Module PowerShellGet -Force command to install the latest version of PowerShellGet. PowerShellGet is a module with commands for discovering,...
Read more