SQL Server : How to improve a 3 join query using costly Non clustered Index Seek

Question

I have a query which makes a inner join of 3 tables.

SELECT DISTINCT A.ID, <OTHER 'A' FIELDS> 
FROM C 
LEFT JOIN B ON C.Bin_ID = B.Bin_ID 
LEFT JOIN A ON B.App_ID = A.App_ID  
WHERE
   ((A.App_NAME LIKE '%%') AND (B.App_ID IS NOT NULL))  
ORDER BY 
    A.App_NAME ASC

Data present in the tables mentioned->

• Table A: 8000 rows
• Table B: 900000 rows
• Table C: 10,00,000 rows

Following is the stats revealed from the SQL Server query plan :

Here Two costly index seeks are used, the upper index seek has the following stats:

Object: B.App_ID [non clustered index, non unique] 
Seek Predicate: B.App_ID = A.App_ID[clustered index,unique]

the bottom index seek has the following stats:

Object: C.Bin_ID [non clustered index, non unique] 
See Predicate: C.Bin_ID = B.Bin_ID[clustered index,unique]

Now this query takes 5 minutes to execute on an average and I am not able to figure out what should be done make it faster since the query uses Index seeks already. {All joins are necessary for the result set}. Need Help !

Answer 1

It looks like you only fetch columns from table A and you only check table B for existing rows.

I guess this would give you the same result and possibly a bit faster:

SELECT A.ID--, <OTHER 'A' FIELDS> 
FROM A
WHERE
   A.App_NAME LIKE '%%' AND 
   A.App_ID IN (SELECT B.App_ID
                FROM B
                  INNER JOIN C
                    ON B.Bin_ID = C.Bin_ID)
ORDER BY 
    A.App_NAME ASC