CODEWITHSUNDEEP
sqlsql-serveroptimization
97ldave
97ldave

Reputation: 5249

SQL optimise IF EXISTS

I am trying to optimise the following:

IF EXISTS (
    SELECT *
    FROM #SearchResults sr
    WHERE 
        sr.src_data_col1 = @SearchCriteria OR
        sr.src_data_col2 = @SearchCriteria OR
        sr.src_data_col3 = @SearchCriteria
    )
    BEGIN

        SELECT *
        FROM #SearchResults sr
        WHERE 
            sr.src_data_col1 = @SearchCriteria OR
            sr.src_data_col2 = @SearchCriteria OR
            sr.src_data_col3 = @SearchCriteria
    END
ELSE
    BEGIN
        SELECT *
        FROM #SearchResults
    END

Is doing 2 SELECT statements going to make it slower? or am I not understanding how EXISTS works correctly?

Thanks in advance.

Upvotes: 0

Views: 262

Answers (2)

Andrew
Andrew

Reputation: 7880

Here you have a quite short way to achieve the same result:

SELECT *
FROM #SearchResults sr
WHERE @SearchCriteria IN (sr.src_data_col1, sr.src_data_col2, sr.src_data_col3)
OR NOT EXISTS (SELECT NULL
        FROM #SearchResults sr
        WHERE @SearchCriteria IN (sr.src_data_col1, sr.src_data_col2, sr.src_data_col3));

If the subquery returns nothing, that condition becomes true and you get all your results.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you are concerned about performance, you can save the data into another temporary table:

SELECT *
INTO #SearchResults_2
FROM #SearchResults sr
WHERE sr.src_data_col1 = @SearchCriteria OR
      sr.src_data_col2 = @SearchCriteria OR
      sr.src_data_col3 = @SearchCriteria;

IF EXISTS (SELECT 1 FROM #SearchResults_2)
BEGIN
    SELECT *
    FROM #SearchResults_2;
END
ELSE BEGIN
    SELECT *
    FROM #SearchResults;
END;

Under most circumstances, such an optimization would be unnecessary. If the search results only have a few tens or hundreds of rows, then it probably is not worth creating another temporary table. However, you can do timings on your system, with your data, to see if the optimization is worth the effort.

Upvotes: 4

Related Questions