Create Temp Table vs Truncate & Insert - Performance

  • 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

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


    Alex Suprun

  • 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

  • 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!

  • 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

  • Thanks for all the feedback.

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

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