Post reply

Which is faster, truncate and load or delete and load?

  • August 23, 2011 at 2:43 am

    #242506

    Hi All,

    We have a table of about 25 GB where data is truncated daily and loaded. Few existing data in the table is updated and few records are inserted daily.

    Which will be a better option, to delete the selected rows and insert the updated values or to truncate the table and load the entire table once again?

    Will the same logic be applicable for smaller tables?

    Will the same solution be applicable for SQL 2000?

  • August 23, 2011 at 3:23 am

    #1371824

    roughly how many rows do you delete, update, insert?

    If you dont absolutely need to clear the whole table then dont use the truncate, the truncate will be fast but the re load would well consume most of the time.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 23, 2011 at 4:24 am

    #1371858

    We update or delete around 10-15% of the total tables.

    For deleting the data we need to search the data using a where clause on last modified date. Isn't it going to be a time consuming task?

  • August 23, 2011 at 4:33 am

    #1371860

    Knowledge Hunter (8/23/2011)

    We update or delete around 10-15% of the total tables.

    For deleting the data we need to search the data using a where clause on last modified date. Isn't it going to be a time consuming task?

    It depends upon whether you have an index on last modified date.

  • August 23, 2011 at 4:40 am

    #1371864

    you might gain performance by partitioning on the date - it all depends if the data you work on can be limited to one or two partitions.

    There are lots of things you can achieve using partitioning.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • August 23, 2011 at 6:28 am

    #1371931

    Dwayne's right an index on the table would help Sql Server locate the rows you want to remove. Base the index on the fields in the where clause of your delete statement.

    Also be aware that deleting or inserting large numbers of rows can cause fragmentation of your indexes, and out of date statistics.

  • August 23, 2011 at 6:45 am

    #1371943

    Thanks a lot for your inputs

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

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