sql: DELETE + INSERT vs UPDATE + INSERT

Question

A similar question has been asked, but since it always depends, I'm asking for my specific situation separately.

I have a web-site page that shows some data that comes from a database, and to generate the data from that database I have to do some fairly complex multiple joins queries.

The data is being updated once a day (nightly).

I would like to pre-generate the data for the said view to speed up the page access.

For that I am creating a table that contains exact data I need.

Question: for my situation, is it reasonable to do complete table wipe followed by insert? or should I do update,insert?

SQL wise seems like DELETE + INSERT will be easier (INSERT part is a single SQL expression).

EDIT: RDBMS: MS SQL Server 2008 Ent

Answer 1

TRUNCATE will be faster than delete, so if you need to empty a table do that instead

You didn't specify your RDBMS vendor but some of them also have MERGE/UPSERT commands This enables you do update the table if the data exists and insert if it doesn't

Answer 2

It partly depends on how the data is accessed. If you have a period of time with no (or very few) users accessing it, then there won't be much impact on the data disappearing (between the DELETE and the completion of the INSERT) for a short while.

Answer 3

It depends on the size of the table and the recovery model on the database. If you are deleting many hundreds of thousands of records and reinstating them vs updating a small batch of a few hundred and inserting tens of rows, it will add an unnecessary size to your transaction logs. However you could use TRUNCATE to get around this as it won't affect the transaction log.

Do you have the option of a MERGE/UPSERT? If you're using MS-SQL you can use CROSS APPLY to do something similar if you don't.

Answer 4

One approach to handling this type of problem is to insert into new table, then do a table Rename. This will insure that all new data is present at the same time.

Answer 5

What if some data that was present yesterdays is not anymore? Delete may be safer or you could end up deleting some records anyway.

And in the end it doesnt really matter which way you go. Unless on the case @kevinw mentioned

Answer 6

Have you considered using a materialized view (MSSQL calls them indexed views) instead of doing it manually? This could also have other performance benefits as an indexed view gives the query optimizer more choices when its constructing execution plans for other queries that reference the table(s) in the view.

Answer 7

Although I fully agree with SQLMenace's answer I do would like to point out that MERGE does NOT remove unneeded records ! If you're sure that your new data will be a super-set of the existing data, then MERGE is great, otherwise you'll either need to make sure that you delete any superfluous records later on, or use the TRUNCATE + INSERT method ... (Personally I'm still a fan of the latter as it usually is quite fast, just make sure to drop all indexes/unique constraints upfront and rebuild them one by one. This has the benefit of the INSERT transaction being smaller and the index-adding being done in (smaller) transactions again later on). (**)

(**: yes, this might be tricky on live system, but then again he already mentioned this was done during some kind of overnight anyway, I'm extrapolating there is no user-access at that time)