Sql-server – Which is quicker: Select of existing row vs Update where no row exists

sql-server-2005sql-server-2008

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.

Best Answer

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;
Related Question