SQL Server Performance Forum – Threads Archive

updating a record that may not exist

Hi all, I have a simple table with a few million records.
Quite often I will have to update some non key field in a record. If that record does not exist, I have to insert it. So I have to select the record first. If it doesn’t exist, insert new record. If it does exist, update it. I can also always delete the record first and then always do the insert. Which would be fastest in general?
And in case there are many more inserts than updates? Thanks for your tips on this!

Inserts and updates are totally dependant upon the table index structure, if the update or insert column has an entity on clustered index then they will be slowed down. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Inserts and updates are totally dependant upon the table index structure, if the update or insert column has an entity on clustered index then they will be slowed down. <hr height="1" noshade id="quote"></blockquote id="quote"> inserts will always have the clustered indx columns also, implicitly or explicitly [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

Sounds like you may be thinking about using a cursor, or a recordset from the client app? Don’t! IF EXISTS (SELECT * FROM table WHERE criteria = ‘blabla’)
UPDATE table SET column = ‘xyz’ WHERE criteria = ‘blabla’
ELSE
INSERT INTO table (column_list) VALUES (value_list)

Is there any UPSERT in 2005? Madhivanan Failing to plan is Planning to fail

I certainly hope not!

Not in 2005, But *probably* in Katmai. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

I predict performance problems.

quote:Originally posted by Adriaan I predict performance problems.

Do you care to explain? Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

As a rule, inserts are quick and updates are slow. Upsert will probably have the same speed as update, right? If updates are factor X slower than inserts, then the inserts in an upsert would also be factor X slower. *** Access/Jet SQL has supported upserts for ages, through UPDATE with a right join from the source table to the target table – at least since Access 97/Jet 3.5: UPDATE target RIGHT JOIN source ON target.key = source.key
SET target.key = source.key, target.col = source.col;

Upsert triggers would be interesting.

You may want to performance test something like this:
UPDATE YourTable SET ValueField = @YourInput WHERE KeyField = @YourKey
IF @@ROWCOUNT = 0
INSERT INTO YourTable (KeyField, ValueField) VALUES (@YourKey, @YourInput)
This has the advantage of eliminating the check for an existing row which should improve performance.
RobC
Technical Architect

Another thought although I don’t like it on principle. You could always attempt the insert then check for the duplicate key error, in which case you would attempt the update. This would give you relatively speedy inserts while I’d expect updates to be slower due to the error propagation and the fact that the insert attempt would have been slow in the first place. If you expect mostly inserts then it may be worthwhile.
RobC
Technical Architect

If speed is critical on inserts and there is little real time need for viewing the data here is an option: Do all inserts and add a identity column (auto-increment), to a transactional table. Later when your gathering your information, create views against this transactional data, or insert into a historical table. Given a table transaction_table:
Identity_Column Data_Key Data_Value
————— ———- ———-
1 5 sdlfk
2 6 asdf
3 4 asdfd
4 5 kdkd
5 4 ee3k3
6 5 34i4ki
A view to retreive the last insert would look something like this: Select Data_Key, Data_Value FROM transaction_table
Where Identity_Column in( Select Max(Identity_Column) from Transaction_Table group by Data_Key) and output: Data_Key Data_Value
———- ———-
6 asdf
4 ee3k3
5 34i4ki
This process will probably only benefit you if the data retrieval is occasional, and the inserts are frequent. Hope this helps.

]]>