CODEWITHSUNDEEP
sqlsql-serverioquery-performancesqlperformance
Mimi.O
Mimi.O

Reputation: 99

SQL Server query inefficient for table with high I/O operations

I'm trying to write an sql script that returns an item from a list, if that item can be found in the list, if not, it returns the most recent item added to the list. I came up with a solution using count and an if-else statement. However my table has very frequent I/O operations and I think this solution is inefficient. Does anyone have a away to optimize this solution or a better approach.

here is my solution:

DECLARE @result_set INT
SET @result_set = (
    SELECT COUNT(*) FROM
    ( SELECT *
        FROM notification p 
        WHERE p.code = @code 
            AND p.reference = @reference 
            AND p.response ='00'
    ) x
)

IF(@result_set > 0)
    BEGIN
        SELECT * 
        FROM notification p 
        WHERE p.code = @code 
            AND p.reference = @reference 
            AND p.response ='00'
    END
ELSE
    BEGIN
        SELECT
        TOP 1  p.*
        FROM notification p (nolock) 
        WHERE p.code = @code 
            AND p.reference = @reference
        ORDER BY p.id DESC
    END

I also think there should be a way around repeating this select statement:

SELECT *
    FROM notification p 
    WHERE p.code = @code 
        AND p.reference = @reference 
        AND p.response ='00'

I'm just not proficient enough in SQL to figure it out.

Upvotes: 1

Views: 92

Answers (2)

Wes H
Wes H

Reputation: 4439

WITH ItemIWant AS (
SELECT *
        FROM notification p 
        WHERE p.code = @code 
            AND p.reference = @reference 
            AND p.response ='00'
),
SELECT *
FROM ItemIWant
UNION ALL
SELECT TOP 1 *
FROM notification p 
WHERE p.code = @code 
    AND p.reference = @reference 
    AND NOT EXISTS (SELECT * FROM ItemIWant)
ORDER BY id desc

This will do that with minimal passes on the table. It will only return the top row if there are no rows returned by ItemIWant. There is no conditional logic so it can be compiled and indexed effectively.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do something like this:

SELECT TOP (1) n.*
FROM notification n 
WHERE p.code = @code  AND p.reference = @reference 
ORDER BY (CASE WHEN p.response ='00' THEN 1 ELSE 2 END), id DESC;

This will return the row with response of '00' first and then any other row. I would expect another column i the ORDER BY to handle recency, but your sample code doesn't provide any clue on what this might be.

Upvotes: 4

Related Questions