SQL Server T-SQL Tuning – Temp Tables, Table Variables and Union

In this article series we will focus on the primary points of T-SQL performance tuning. In this first installment I will lopok at tuning temp tables, table variables and the Union command.

Temp Tables and Table Variables

Most database developers are used to breaking down code into small chunks using  temp tables or table variables  to simplify complex logic, however this can result in a performance impairment :

  1. Impact on  I/O subsystem resources (SAN or local storage) due to the increase of page latch waits and page I/O latch waits which are considered the worst type of waits as they can lead to intensive TempDB contentions due to high page allocation requests and so either the Global Allocation Map (GAM) , Shared Global Allocation Map (SGAM) or Page Free Space (PFS) can end up at a standstill.
  2. Impact on CPU utilization due to Cxpacket waits resulting from insufficient indexing on temp tables that can be mitigated if temp tables are well indexed with clustered and non-clustered indexes.

Thus it is preferable to limit the usage of temp tables and when a temp table cannot be avoided consider the below precautions:

  • Use temp tables (Create table #Temp) as opposed to using a table variables (Declare @table table) since you can create indexes on temp tables.
  • Use Temp table as small staging tables with a small volume of data to limit its performance impact.
  • Ensure there is sufficient indexing (clustered or non-clustered) if the temp table is used within the inner join , group by , order by or where conditions.

Therefore what are possible alternatives here to avoid using temp tables and table variables?

  1. CTE expressions.
  2. Sub queries.
  3. Physical table (Schema Table) created in the database schema, instead of creating a temp table at TempDB.
  4. Table Parameters which has been available from SQL Server 2008.
Examples :

First, in a new database ‘Workshops’ we will create the new tables:

 --Create new Tables
use Workshops
CREATE TABLE [dbo].[Employees](
      [empid] [int] IDENTITY(1,1) NOT NULL,
      [empname] [nvarchar](100) NULL,
      [deptid] [int] NULL,
      [Salary] [float] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
( [empid] ASC )
WITH
(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Departments](
      [deptid] [int] IDENTITY(1,1) NOT NULL,
      [deptname] [nchar](10) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
 (  [deptid] ASC   )
WITH
(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] )
ON [PRIMARY]
GO
 


Using table variables :

alter procedure Performance_Issue_Table_Variables
as
begin
SET NOCOUNT ON;
declare @table table(empid int, empname varchar (25),Department varchar (25) ,Salary int)
insert into @table select S.empid,S.empname,T.deptname,S.salary from Employees s inner join Departments T ON S.deptid =T.deptid
SELECT COUNT (empid) ,Department,Salary  FROM @table GROUP BY Department,Salary HAVING Salary>2000
end
  


Using a temp table :

Create procedure Performance_Issue_Table_Variables
as
begin
SET NOCOUNT ON;
create table #table (empid int, empname varchar (25),Department varchar (25) ,Salary int)
create clustered index #table_index1 on #table (empid asc )
create nonclustered index #table_index2 on #table (Salary) include (Department,empid )
insert into #table select S.empid,S.empname,T.deptname,S.salary from Employees s
inner join Departments T ON S.deptid =T.deptid
SELECT COUNT (empid) ,Department,Salary  FROM #table GROUP BY Department,Salary HAVING Salary>2000
DROP TABLE #table
end
    


Using a CTE expression :

Create procedure Performance_Solution_CTEexpression
as
begin
SET NOCOUNT ON;
With temp as
(
select S.empid,S.empname,T.deptname as Department,S.salary from Employees s inner
join Departments T ON S.deptid =T.deptid
)
SELECT COUNT (empid) ,Department,Salary  FROM temp GROUP BY Department,Salary HAVING Salary>2000
end
    

Using table parameters

Table parameters can be implemented with three steps. First, create a new table data type:

create type Specialtable as table
(EmployeeID int NULL,
EmployeeName Nvarchar (50) Null )
      

Next, create a stored procedure which accepts the table as an input:

create  procedure Performance_Solution_Table_Paramters @Temptable Specialtable Readonly
as
begin
select * from @Temptable
end

Finally, execute the stored procedure :

declare @temptable_value specialtable
insert into @temptable_value select '1','Jone' union select '2', 'Bill'
exec dbo.SP_Results @temptable=@temptable_value
 


Using Subqueries

 Create procedure Performance_Solution_SubQuery
as
begin
SET NOCOUNT ON;
SELECT COUNT (empid) ,S.Department,Salary  FROM
(select S.empid,S.empname,T.deptname as Department,S.salary from Employees s inner join Departments T ON S.deptid =T.deptid) S
GROUP BY Department,Salary HAVING Salary>2000
end
 


Using physical tables (schema tables) 

create table schema_table (empid int, empname varchar (25),Department varchar (25) ,Salary int)
create clustered index schema_table_index1 on schema_table (empid asc )
create nonclustered index schema_table_index2 on schema_table (Salary) include (Department,empid )
insert into schema_table select S.empid,S.empname,T.deptname,S.salary from Employees s inner join Departments T ON S.deptid =T.deptid
go
Create procedure Performance_Solution_PhysicalTables
as
begin
SET NOCOUNT ON;
SELECT COUNT (empid) ,Department,Salary  FROM schema_table GROUP BY Department,Salary HAVING Salary>2000
end
  

The UNION Command

The Union Command has a similar impact on the IO subsystem (such as page  IO latch waits and page latch waits) as using temp tables, but many database developers still to use the Union command to to tackle complex business logic.

Alternatives/Improvements to Union :

  • Replace Union commands with the Case when clause which can be done for both aggregate and detailed   queries.
  • Use Dynamic queries to get the rich power of sp_executesq for saving time consumed on the estimation of query execution plan on each run, and controlling the number of Union commands according to the parameters passed to the stored procedure using If else statements to decide which set of select queries are appropriate for a set of parameters.
  • Order the Select statements used within Union starting with the lightest select queries to reduce the page latch patch waits consumed by heavy queries.

Examples :

Obtaining a grid of results using a poorly optimized Union commmand

create procedure Poor_Performing_UnionSP
as
begin
SET NOCOUNT ON;
select S.empid,S.empname,T.deptname,S.salary from Employees s inner join Departments T ON S.deptid =T.deptid WHERE T.deptid>1 and S.salary  5000
UNION
select S.empid,S.empname,'Management deparments' as deptname,S.salary from Employees s inner join Departments T ON S.deptid =T.deptid WHERE T.deptid=1 and S.salary  >10000
end


Obtaining a grid of results using a Case When :

create procedure PerformantSP_Grid_Results_Using_CaseWhen
AS
BEGIN
select S.empid,S.empname,
case when T.deptid>1 and S.salary  5000  then T.deptname
when T.deptid=1 and S.salary  >10000 then 'Management deparments'  end as deptname
,S.salary
from Employees s inner join Departments T ON S.deptid =T.deptid
END
GO


Obtaining aggregate results using Union:

create procedure Poor_Performing_Union_Aggregate_Results
as
begin
SET NOCOUNT ON;
select count (S.empid)as Employee_count,T.deptname,S.salary from Employees s
inner join Departments T
ON S.deptid =T.deptid WHERE T.deptid>1 and S.salary   10000  group by T.deptname,S.salary
end


Alternvatively, the aggregate results can be obtained using Case When:

create procedure PerformantSP_Aggregate_Results_Using_CaseWhen
as
begin
SET NOCOUNT ON;
select sum (case when   T.deptid>1 and S.salary   10000  then 1 else 0 end)
as Employee_count2
,T.deptname,S.salary
from Employees s inner join Departments T ON S.deptid =T.deptid
group by T.deptname,S.salary
end

In the next article in the series we will look at optimizing TVF and Scalar functions.




Related Articles :

29 Responses to “SQL Server T-SQL Tuning – Temp Tables, Table Variables and Union”

  1. Very helpful and valuable

  2. Really, your formance Guidelines helped me in enhancing some of my queries , I do feel interested to follow up your next blogs on this regard

  3. Wow lots of work.Informative article!!

  4. I Like the way you write your articles.
    I came across something which i don’t know and thought would be great to know more about it. your input will be greatly appreciated.

  5. valuable information…

  6. Thank you for posting these article
    It’s a helpful for me

  7. Good one!!
    I am interested in training for MSSQL DBA training.

  8. You are doing a great job…Thank you

  9. Very helpful and useful, specially I used some of this techniques ,but without begin aware of the explanation.

  10. Awesome! I have never found info as easy to understand as what you have provided. Thank you Shehap!

  11. Just a side note, the code given under the part starting with Obtaining a grid of

    results using a Case When :

    appears to refer to a sample with no UNION clause involved…

    • Yes, right and that typically what we are trying to do by replacing Union commands by case when commands in case that we have select statements sharing the same tables

      Let me know if any further help is needed

  12. your blogs help me a lot … really thankful to you …

  13. Really enjoying this article . thank you

  14. Really enjoying following you. You seem very upbeat

  15. I’ve read your blog and have found it very insightful and have always used it as a go to resource when I needed to know anything SQL related… Thanks so much!

  16. I really enjoy your article !

  17. Quite useful!

  18. very good article it helps me a lot thanks for posting it !

  19. I read your blog everyday and I really appreciate your

  20. Is it better for performance to create the indexes on a #temp table before or after populating the table using an INSERT?

    Of course, creating the indexes first is not an option if you create the temp table using a CTAS (Create Table as Select) statement.

    • Yes ,it is better to create clustered indexes on #TempDB table before insert to avoid any heap table case and thus avoid any table scan but you can defer creating non clustered indexes after insertion to avoid any additional IO cost for clustered indexes that can impact negatively on DML performance and this is an ultimate abstract should be considered largely for DBs having intensive OLTP transactions (DML transactions)

      Let me know if any further help is needed

  21. very interesting blog!!!

  22. your scripts are great and valuable .Thanks

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |