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!

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 |