Efficient Update or Insert 3

[JontyMC]

Whats the most efficient way of entering data to a table - updating if exists and inserting if doesnt?

Jon

"I don't regret this, but I both rue and lament it.

 

[chiph]

It depends.

How many records are you trying to update/insert?
Are they grouped together (all updates then all inserts) or mixed randomly?
Is a transaction necessary?

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[JontyMC]

Will be around 4000 records, mixed randomly. Will transaction be quicker?

At the moment the code goes:

select count(*) from table where id = @id

if this > 0, update
else insert

I'm sure there must be a better way.

Jon

"I don't regret this, but I both rue and lament it.

 

[johnherman]

Generally speaking, if SELECT or READ = 1 unit, then
INSERT or WRITE = 2 units, DELETE = 2 units, and UPDATE or REWRITE = 3 units. There are other factors to be considered such as number and type of indexes, record/row size, etc.

So UPDATE is more efficient than DELETE and INSERT. However, if you can truncate the table before loading (history doesn't matter), then truncating and INSERTing
is cheaper the UPDATEing in place.

Why not run a little test in your environment to see?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[JontyMC]

It has to be update or insert, as the new data may not be the complete dataset. I've never done this before, just looking for some clean, efficient code to do it.

Jon

"I don't regret this, but I both rue and lament it.

 

[druer]

1. Transaction would be necessary if you want all of the activity to either be committed or rolled back as a single unit. In other words if you insert/udpate the first 3,000 and then get an error on the 3001'st item, and you don't care and will just proceed then no transaction is necessary. However, if you can't insert the 3001'st item and would then not want any of the previous 3000 to still be in the databse then you should use a transaction.

2. Will transaction be quicker? No a transaction will not be quicker, in fact it would be slower because it would retain the lock on any items until all 4000 are completed, as opposed to releasing them after each one. That would only matter if other queries/users might still be trying to touch the table while this bulk load is going on. However, speed usually the issue, its the data quality as expressed in #1. You either need or don't need to use the Transaction, and if you do then you have to live with the performance hit.

3. Depending on how many rows you already have in the table it might make sense to drop your indexes for the table, do your bulk insert, then re-CREATE your indexes. This also depends if the table is still "live" during the bulk loading process. If your system is essentially down during the load, then it would speed up the loading to have the indexes dropped, and then re-create them, since you will want to have them rebuilt after the load anyway. However, if your system is running 24/7 and the 4000 rows are just a drop in the bucket of what the table contains total, then you definitely don't want to do this.

 

[JontyMC]

OK, thanks for the input. I'm looking for some basic info, how can I tell whether to insert or update?

Jon

"I don't regret this, but I both rue and lament it.

 

[gmmastros]

If you have a unique identifier in the data, then your best bet would be to...

If Exists(Select 1 From Table Where Field = <Unique Identifier>)
  Begin
    [green]-- The record exists, so update it[/green]
    Update Table
    Set Blah....
  End
Else
  Begin
    [green]-- The record does not exist[/green]
    Insert Into blah....
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JontyMC]

Thats what I was after, thanks. Is this the most efficient way to code it?

Is there a way perhaps to say:

Try update, if fails insert. Would that be quicker?

Jon

"I don't regret this, but I both rue and lament it.

 

[gmmastros]

In my opinion, this is the most efficient way to handle it.

I attended a Microsoft Launch seminar a couple weeks ago. One of the SQL sessions showed how SQL 2005 has try catch blocks and the example they used was for saving data.

So... until you start using SQL2K5, this is probably your most efficient way of deling with it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom