CODEWITHSUNDEEP
sql-serverperformance
tesicg
tesicg

Reputation: 4053

What's faster creating temp table or UNION ALL?

I'm trying to make some query in MS SQL Server 2008 R2 faster and there are 2 ways:

1) By creating temp table:

IF OBJECT_ID('tempdb..#Rec') IS NOT NULL
BEGIN
DROP TABLE #Rec
END

CREATE TABLE #Rec
(
    ID int NULL,
    DateBeg datetime NULL,
    DateEnd datetime NULL,
    Artist varchar(200) NULL,
    DescriptionFull text NULL,
    ActionPlaceID int NULL,
    ActionTypeID smallint NULL,
    Visible tinyint NULL,
    Created datetime NULL,
    DateList varchar(4000) NULL,
    DatesAsPeriod tinyint NULL,
    ShowReservLegend tinyint NULL,
    ProviderID int NULL
)

INSERT INTO #Rec
SELECT ID,
    DateBeg,
    DateEnd,
    Artist,
    DescriptionFull,
    ActionPlaceID,
    ActionTypeID,
    Visible,
    Created,
    DateList,
    DatesAsPeriod,
    ShowReservLegend,
    ProviderID
FROM [ConcertDev].[dbo].[T_Action]
WHERE DateBeg BETWEEN '2012-01-01' AND '2012-06-01'

INSERT INTO #Rec
SELECT ID,
    DateBeg,
    DateEnd,
    Artist,
    DescriptionFull,
    ActionPlaceID,
    ActionTypeID,
    Visible,
    Created,
    DateList,
    DatesAsPeriod,
    ShowReservLegend,
    ProviderID
FROM [ConcertDev].[dbo].[T_Action]
WHERE DateBeg BETWEEN '2013-01-01' AND '2013-06-01'

SELECT * FROM #Rec

2) By using UNION ALL:

SELECT ID,
    DateBeg,
    DateEnd,
    Artist,
    DescriptionFull,
    ActionPlaceID,
    ActionTypeID,
    Visible,
    Created,
    DateList,
    DatesAsPeriod,
    ShowReservLegend,
    ProviderID
FROM [ConcertDev].[dbo].[T_Action]
WHERE DateBeg BETWEEN '2012-01-01' AND '2012-06-01'

UNION ALL

SELECT ID,
    DateBeg,
    DateEnd,
    Artist,
    DescriptionFull,
    ActionPlaceID,
    ActionTypeID,
    Visible,
    Created,
    DateList,
    DatesAsPeriod,
    ShowReservLegend,
    ProviderID
FROM [ConcertDev].[dbo].[T_Action]
WHERE DateBeg BETWEEN '2013-01-01' AND '2013-06-01'

I've expected UNION ALL could be much faster, but it wasn't. It's faster for only 1 second.

The number of records is 6147. By using temp table method the time of execution is 18 seconds. By using UNION ALL method the time of execution is 17 seconds.

So, is this correct approach to speed up query?

Upvotes: 1

Views: 3111

Answers (2)

Altaf Sami
Altaf Sami

Reputation: 846

You can do like this also:

SELECT ID,
DateBeg,
DateEnd,
Artist,
DescriptionFull,
ActionPlaceID,
ActionTypeID,
Visible,
Created,
DateList,
DatesAsPeriod,
ShowReservLegend,
ProviderID

into #Rec

FROM [ConcertDev].[dbo].[T_Action]
WHERE DateBeg BETWEEN '2012-01-01' AND '2012-06-01'
OR
DateBeg BETWEEN '2013-01-01' AND '2013-06-01' 

SELECT * FROM #Rec
DROP TABLE #Rec

Upvotes: 1

Amit
Amit

Reputation: 15387

Probably, provided that you use UNION ALL. By default UNION implies DISTINCT which could cause a extra sorting step that could be equally expensive as the temp table.

But there are always a lot of "it depends" when it comes to performance, so if there are some deviations from the scenario as you described it, the real answer may be different.

One advantage with temp tables i can think of is that you can apply indexes to them. So that should help when dealing with lots of data where you need to get results back as quick as possible.

Upvotes: 0

Related Questions