Create Temp Table vs Truncate & Insert - Performance

Question

Post reply

Create Temp Table vs Truncate & Insert - Performance

homebrew01

SSC Guru

Points: 55548
More actions
March 26, 2015 at 1:07 pm

#316541

I have a process that loops through table A, compares to Table B, and put differences into a temp table #TempTable, which then inserts into Table C. This runs every few seconds as a way of keeping data in sync (long story). There are often 0 records, but every minute or 2 there will be 1
Anyway, considering the frequency it runs, is there an overhead difference between create/drop the temp table each time, vs truncate, insert ??
Is there a better alternative to a temp table ??
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 1

Get rid of the loop. This should be your performance concern.

Alex Suprun

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

There is no difference between truncating a temp table vs dropping/recreating it. Truncating and dropping/recreating a table can be measured in milliseconds.

As Alexander said - it would be good to lose the loop if possible.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323518 More actions · Answer 3

my fast proof of concept: drop and recreate is definitely slower

in a measly 1000 iterations,these were my stats:

2113 milliseconds for Drop and Recreate

543 milliseconds for Truncate Existing Table

and my test harness:

SET STATISTICS IO OFF

SET NOCOUNT ON

DECLARE @i int = 0

DECLARE @Start datetime = getdate();

SET @Start = getdate();

WHILE @i <1000

BEGIN

IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL

DROP TABLE [dbo].[#tmp]

--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data

SELECT TOP (@i % 5) name INTO #tmp FROM sys.columns

SET @i=@i + 1

END

PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Drop and Recreate'

IF OBJECT_ID('tempdb.[dbo].[#tmp2]') IS NOT NULL

DROP TABLE [dbo].[#tmp2]

CREATE TABLE [dbo].[#tmp2] (

[name] SYSNAME NULL)

SET @Start = getdate();

SET @i = 0;

WHILE @i <1000

BEGIN

TRUNCATE TABLE [#tmp2]

--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data

INSERT INTO [#tmp2]

SELECT TOP (@i % 5) name FROM sys.columns

SET @i=@i + 1

END

PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Truncate Existing Table'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Lowell (3/26/2015)
my fast proof of concept: drop and recreate is definitely slower
in a measly 1000 iterations,these were my stats:
2113 milliseconds for Drop and Recreate
543 milliseconds for Truncate Existing Table
and my test harness:
SET STATISTICS IO OFF
SET NOCOUNT ON
DECLARE @i int = 0
DECLARE @Start datetime = getdate();
SET @Start = getdate();
WHILE @i <1000
BEGIN
IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL
DROP TABLE [dbo].[#tmp]
--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data
SELECT TOP (@i % 5) name INTO #tmp FROM sys.columns
SET @i=@i + 1
END
PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Drop and Recreate'
IF OBJECT_ID('tempdb.[dbo].[#tmp2]') IS NOT NULL
DROP TABLE [dbo].[#tmp2]
CREATE TABLE [dbo].[#tmp2] (
[name] SYSNAME NULL)
SET @Start = getdate();
SET @i = 0;
WHILE @i <1000
BEGIN
TRUNCATE TABLE [#tmp2]
--some percentage (1/5 ) of iterations will be skipped as no rows to simulate occasional data
INSERT INTO [#tmp2]
SELECT TOP (@i % 5) name FROM sys.columns
SET @i=@i + 1
END
PRINT CONVERT(varchar,datediff(ms,@Start,getdate())) +' milliseconds for Truncate Existing Table'

I stand corrected.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

homebrew01

SSC Guru

Points: 55548

March 26, 2015 at 5:58 pm

#1786719

Thanks for all the feedback.