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

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 :

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.
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 https://hotcanadianpharmacy.com 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?

CTE expressions.
Sub queries.
Physical table (Schema Table) created in the database schema, instead of creating a temp table at TempDB.
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.

]]>

Temp Tables and Table Variables

The UNION Command

Leave a comment Cancel reply