Optimizing SQL Stored Procedure Query for better performance

Question

Is there a way to optimize the query below as its took quite awhile to retrieve the massive records from the table (T_School_Class) and (T_School) I had created indexes for Name as well as SchoolCode for T_School. In additional, Temp Table was also created.

        SELECT Distinct (S.SchoolCode) As Code, Name from T_STU_School AS S
        LEFT JOIN T_STU_School_Class AS SC ON S.SchoolCode = SC.SchoolCode
        WHERE S.SchoolCode  IN
        (SELECT SchoolCode FROM @MainLevelCodeTemp)
        AND [Status] = 'A' 
        AND Name LIKE @Keyword
        AND (@AcademicCode = '' OR SC.AcademicLevel IN (@AcademicCode))
        Order BY Name ASC;

Answer 1

all the imperatives in the sproc are a waste, you're just forcing SQL to scan T_STU_School multiple times, all that logic should just be added to the where clause:

SELECT Distinct (S.SchoolCode) As Code, Name from T_STU_School AS S
LEFT JOIN T_STU_School_Class AS SC ON S.SchoolCode = SC.SchoolCode
WHERE ((@MainLevelCode LIKE '%J%' AND S.MixLevelType IN ('T1','T2','T6'))
   OR  (@MainLevelCode LIKE '%S%' AND S.MixLevelType IN ('T1','T2','T5','T6'))
   OR  (@MainLevelCode LIKE '%P%' AND S.MixLevelType IN ('T1','T2','T6'))
   OR  (MainLevelCode IN (SELECT Item FROM [dbo].[SplitString](@MainLevelCode, ',')))
   OR  @MainLevelCode = '')
AND [Status] = 'A' 
AND (@Keyword = '' OR Name LIKE @Keyword)
AND (@AcademicCode = '' OR SC.AcademicLevel IN (@AcademicCode))
Order BY Name ASC;

..the reason both tables are still being scanned per your execution plan even though you've created indexes on Name and SchoolCode is because there's no criteria on SchoolCode that would reduce the result set to less than the whole table, and likewise with Name whenever it is blank or starts with a "%". to prevent the full table scans you should create indexes on:

T_STU_School (Status, Name)
T_STU_School_Class (MixLevelType, SchoolCode)
T_STU_School_Class (MainLevelCode, SchoolCode)

..also any time you have stuff like (y='' OR x=y) in the where clause it's a good idea to add an OPTION (RECOMPILE) to the bottom to avoid the eventual bad plan cache nightmare.

..also this line is probably a bug:

AND (@AcademicCode = '' OR SC.AcademicLevel IN (@AcademicCode))

IN won't parse @AcademicCode so this statement is equivalent to SC.AcademicLevel=@AcademicCode

Answer 2

You definitely need an index on T_STU_SCHOOL.SchoolCode. Your query plan shows that 65% of the query time is taken from the index scan that results from the join. An index on the SchoolCode column should turn that into an index seek, which will be much faster.

The Name index is not currently being used, probably because you're passing in values for @keyword that start with a wildcard. Given that Name is on the T_STU_School table, which has a small number rows, you can maybe afford a table scan there in order to use wildcards the way you want to. So you should be able to drop the Name index.