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.




Array

No comments yet... Be the first to leave a reply!