SQL Server Performance

updating a record that may not exist

Discussion in 'T-SQL Performance Tuning for Developers' started by jeroen, Apr 12, 2007.

  1. jeroen New Member

    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!
  2. satya Moderator

    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
    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.
  3. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />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.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /> inserts will always have the clustered indx columns also, implicitly or explicitly [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  4. Adriaan New Member

    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'
    INSERT INTO table (column_list) VALUES (value_list)
  5. Madhivanan Moderator

    Is there any UPSERT in 2005?


    Failing to plan is Planning to fail
  6. Adriaan New Member

    I certainly hope not!
  7. Roji. P. Thomas New Member

  8. Adriaan New Member

    I predict performance problems.
  9. Roji. P. Thomas New Member

  10. Adriaan New Member

    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;
  11. Adriaan New Member

    Upsert triggers would be interesting.
  12. RobC New Member

    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.

    Technical Architect
  13. RobC New Member

    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.

    Technical Architect
  14. vindicator New Member

    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.

Share This Page