Which is quicker: Select of existing row vs Update where no row exists?

Which would run the fastest?

• Select from a table using the primary key (integer, clustered index, 1,000,000+ rows)

• 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:

• Select filter criteria

• If filter criteria match then:

• Attempt an update

• 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:

• Attempt an update

• If no records updated, then:

• Select filter criteria

• 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 C

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;

StackExchange Database Administrators Q#5849, answer score: 7