Update then Insert or Delete and Insert?

[cmgaviao]

I have an application that moves data into a temp table to work it over and then moves data from the temp table into the actual production data.

Sometimes the data being imported already exists in the production table. Depending on the month, it could be just a few lines from the TEMP table, or it could be the entire temp table that already exists.

In order to move the data from the temp table, I use 2 SQLs:

1. Update Production table where key in (select keys from temp table)

After the existing data is updated, I can insert the new data:

2. Insert into production table from Temp Table where Temp Key not exists in (select keys from Production)

I don't know if this helps or not, but since the production tables are for reporting purposes, they are never purged (we must keep at least the previous 2 years of data). So production table keeps getting larger and larger. The data in the temp table could be any range from weeks to months.

The big question: Is my current method faster, or would it be faster to do:

1. Delete production table where key in (select keys from temp)

2. Insert into production from Temp

TIA for any assistance.

 

[mkrausnick]

I doubt that it makes much difference from the speed perspective, but the database might grow faster with more wasted space in the middle, under the second method. It depends on the number and length of varchar fields in the design. Thus, the database might require optimization more often.

Think about this scenario:
One 512 byte record in production.
One each 384 bype and 512 byte records in temp file, the 512 byte record being an update to the existing production record.

Method two could:
1. Delete the 512 byte record.
2. Add the 384 Byte record into the page formerly occupied by the 512 byte record,
3. Add the updated 512 byte record onto the end of the db space.

Now you have 128 wasted bytes that will remain unusable until you optimize/shrink the db.

Actually, method one might be faster anyway, depending on whether you're running on a network and whether unchanged fields are actually updated from temp to production.

Anyway, my intuition tells me method one is better.



Mike Krausnick