Create Temp Table vs Truncate & Insert - Performance

• homebrew01

  SSC Guru

  Points: 55549

  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

• Alexander Suprun

  SSCertifiable

  Points: 6253

  More actions

  March 26, 2015 at 2:00 pm

  #1786677

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

  ---

  Alex Suprun

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  March 26, 2015 at 2:24 pm

  #1786685

  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: 323527

  More actions

  March 26, 2015 at 3:06 pm

  #1786696

  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

  March 26, 2015 at 4:17 pm

  #1786712

  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: 55549

  More actions

  March 26, 2015 at 5:58 pm

  #1786719

  Thanks for all the feedback.