How to optimize this SQL Server query

Question

I have following query, which is working as expected but taking approx 3 seconds to execute. Reason is large number of records. Can somebody please suggest any steps in order to improve performance?

Explanation :

1. Check to see value using Comp id and Default_Comp = 1
2. If not found, ignore the Default_Comp and check only based on Comp id
3. Still not found, ignore the join with table 2 and try to get by Comp id.

My code:

DECLARE @Finished_Comp VARCHAR(MAX) = NULL;

SELECT @Finished_Comp = MIN(tbl2.Finished_Comp) 
FROM Table1 tbl1 
INNER JOIN Table2 tbl2 ON tbl1.Sav_ID = tbl2.Sav_ID
WHERE Comp_ID = @Comp_ID AND tbl1.Default_Comp = 1 

IF @Finished_Comp IS NULL 
BEGIN   
    SELECT @Finished_Comp = MIN(tbl2.Finished_Comp) 
    FROM Table1 tbl1 
    INNER JOIN Table2 tbl2 ON tbl1.Sav_ID = tbl2.Sav_ID 
    WHERE Comp_ID = @Comp_ID 
END

IF @Finished_Comp IS NULL  
BEGIN 
    SELECT @Finished_Comp = MIN(Finished_Comp) 
    FROM Table1 tbl1 
    WHERE Comp_ID = @Comp_ID AND @Finished_Comp != '' 
END

I tried to use COALESCE, but it's returning wrong results for Finished_Comp

Answer 1

You say in the comments

I strongly believe the query can be changed to some extent so that no multiple queries need to be executed.

Yes you're right.

SELECT @Finished_Comp = COALESCE(MIN(CASE WHEN tbl1.Default_Comp = 1 THEN tbl2.Finished_Comp END), 
                                 MIN(tbl2.Finished_Comp), 
                                 MIN(CASE WHEN tbl1.Finished_Comp <> '' THEN tbl1.Finished_Comp END))
FROM   Table1 tbl1
       LEFT JOIN Table2 tbl2
              ON tbl1.Sav_ID = tbl2.Sav_ID
WHERE  tbl1.Comp_ID = @Comp_IDV 

But at best this will only reduce execution time to a third of current (for the case that all three queries need to be executed).

You should consider adding indexes on

• Table1 - Comp_ID, Sav_ID INCLUDE (Default_Comp, Finished_Comp)
• Table2 - Sav_ID INCLUDE (Finished_Comp)

For potentially much larger improvements.