Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Which would run the fastest?

  1. Select from a table using the primary key (integer, clustered index, 1,000,000+ rows)
  2. Attempt an update on a different table, where the row does not exist? (where clause on integer primary key, clustered index, 200,000+ rows)

Background

We currently have some procedures that need to maintain a filtered copy of their table.

Tables involved:

  • [MasterTable] that contains the filter criteria
  • [ChildTable] to be filtered
  • [ChildFilterTable] to hold the filtered records

This is currently being done by:

  1. Select filter criteria
  2. If filter criteria match then:
    1. Attempt an update
    2. If no records updated, insert a new record

Example SQL:

DECLARE @FilterValue INT

/* Get FilterValue to check */
SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

IF @FilterValue = 123
BEGIN
    /* Attempt update */
    UPDATE [ChildFilterTable] SET
        ...
    WHERE ChildID = @IDFromChildTable

    IF @@ROWCOUNT = 0
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Proposed change

Change to:

  1. Attempt an update
  2. If no records updated, then:
    1. Select filter criteria
    2. If filter criteria match then: insert a new record

So:

DECLARE @FilterValue INT

/* Attempt update */
UPDATE [ChildFilterTable] SET
    ...
WHERE ChildID = @IDFromChildTable

IF @@ROWCOUNT = 0
BEGIN
    /* Get FilterValue to check */
    SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

    IF @FilterValue = 123
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Note: Business rules confirm that the filter value will never change once setup on the [MasterTable] so we don't need to worry about updating a value which does not match the filter record (ie: if it's in the ChildFilterTable, we want to update it.

share|improve this question
5  
Your proposed change sounds a lot better to me than your existing code. I've actually resolved three separate deadlock issues just by changing similar code in the same way - from a select to see if it should update or insert, to an update and then an insert if no rows were affected. As for @gbn's answer, perhaps you could have two versions of the proc that are called depending on the underlying version of SQL Server, because MERGE is absolutely the best way to handle this IMHO. –  Aaron Bertrand Sep 16 '11 at 14:20

3 Answers 3

up vote 7 down vote accepted

Can you not use the MERGE statement added with SQL Server 2008 to "UPSERT" in one atomic operation?

DECLARE @FilterValue INT

;MERGE 
INTO ChildFilterTable AS CFT
USING  (your filter, source thing here)
                  ON (CFT.ChildID = ...)
WHEN MATCHED
    THEN update stuff
WHEN NOT MATCHED BY TARGET
    THEN insert stuff;
share|improve this answer
    
Unfortunately we're on a mixture of 2005 and 2008 at the moment and need to maintain backward compatibility. Q: Do you know if under the hood the merge operates faster than an update if rowcount = 0 insert? –  Andrew Bickerton Sep 16 '11 at 13:18
1  
Yes, I'm sure it's quicker. It is one operation, not 2. But I haven't benchmarked it. –  gbn Sep 16 '11 at 13:41

Did you check how expensive your INSERTs/UPDATEs are?

If you care about performance, I would always call the INSERT or UPDATE first, depending on what is more likely to be necessary in your scenario. So if you expect your data to be in 80% of the cases already there, call the UPDATE first, then on fail the INSERT. If the record will usually have to be inserted most of the cases, call the INSERT first.

share|improve this answer
    
In our tests we have found that the update is generally more likely and so we by default try the update first... there are some [ChildTables] where that is not the case, but performance wise we've found the update call where no rows match is quicker than an if exists call. –  Andrew Bickerton Sep 16 '11 at 13:34

What you can try is set statistics time on at the start of both the queries, and review the results. Remember that if the query is getting cached, you will see different results, so you may have to clear the cache while testing to see how they work out.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.