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

In the previous post
, we've illustrated the differences between the execution plans generated by joining a table variable and by a temp table. In this post, we are going to look closer about the differences, and get a better understanding about the mechanisms behind those differences.

Let's recap our scripts and their execution plans.

DECLARE @CET TABLE
(
       [BusinessEntityID] int
       ,[AddressID] int
       ,[AddressTypeID] int
       ,[Name] [nvarchar](50)
)

INSERT INTO @CET
SELECT
       BEA.BusinessEntityID
       ,BEA.AddressID
       ,BEA.AddressTypeID
       ,ADT.Name
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 @CET CS
INNER JOIn [Person].[BusinessEntity] BE ON CS.BusinessEntityID = BE.BusinessEntityID

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




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





The whole data flow is quite similar between the two:

  1. The table variable or temp table is used to join the table Person.BusinessEntity.
  2. The above result is used to join table Person.Address.

I've circled the differences between the two screenshots, with two difference colors. The blue circles indicate that different join types (Nested Loop VS Hash Match) are used in the two scripts, resulting in Clustered Index Seek VS Clustered Index Scan respectively as circled in red.

To understand the above differences, you need to know what Nested Loop (NL) and Hash Match (HM) are, and then you'll understand why the Clustered Index Seek (CISK) or Clustered Index Scan (CISN). If you don't, well don't worry because the execution plan itself has already told us enough information for us to understand. If you hover over the first NL from right in the execution plan window, you'll see the below popped information


As highlighted in yellow in the above screenshot, NL scans each row in the outer input - which is the BusinessEntityID column in the table variable @CET - and then scans the inner input (which is the BusinessEntityID column in table Person.BusinessEntity) to find the match, and this is why we see Clustered Index Seek is used as only a range of values is scanned as illustrated below



If we summarise the above analysis, the following conclusions can be made regarding table variable join script:

  • A NL is committed between @CTE and Person.BusinessEntity. A table scan is conducted on @CTE as it's a heap table, and for each row in the column BusinessEntityID, the same column in table Person.BusinessEntity is scanned to output the matching rows.
  • Based on the above output, for each row of the AddressID column in @CTE, the same column in table Person.Address is scanned to output the matching rows.
We'll keep discussing the execution plan for temp table, as well as some derived scripts and their execution plans to help us understand better about the differences between table variable and temp table along with their variations.

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