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;

MSSQL, The query is plan to retrieve all the records out base on the user search 'Keyword', but if the user does not enter any 'Keywords'. It will display all the records in the database. — Stella
– Stella, Commented Jul 30, 2017 at 3:13
@Stella I think what he meant by query plan is query execution plan : learn.microsoft.com/en-us/sql/relational-databases/performance/… .. using this tool, we can find which part of the query gives the biggest cost — samAlvin
– samAlvin, Commented Jul 30, 2017 at 3:15
@samAlvin hey thanks so much, i had posted the execution plan already. =) — Stella
– Stella, Commented Jul 30, 2017 at 3:27
We need the table definitions and the index definitions. Also, how many rows are returned and how long does it take? Finally, how much faster do you need it to be? — RBarryYoung
– RBarryYoung, Commented Jul 30, 2017 at 3:59

gordy · Accepted Answer · 2017-07-30 07:23:35Z

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

edited Jul 30, 2017 at 7:23

answered Jul 30, 2017 at 4:13

gordy

9,9713 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stella Over a year ago

Thanks gordy, but when i tried to modify the logic into 1. this particular sentence fails. OR (MainLevelCode IN [dbo].[SplitString](@MainLevelCode, ','))

gordy Over a year ago

@Stella it should be OR (MainLevelCode IN (SELECT Item FROM dbo.SplitString(@MainLevelCode, ',')))

Paul Keister · Accepted Answer · 2017-07-30 19:48:28Z

0

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.

edited Jul 30, 2017 at 19:48

answered Jul 30, 2017 at 6:10

Paul Keister

13.1k5 gold badges50 silver badges77 bronze badges

Collectives™ on Stack Overflow

Optimizing SQL Stored Procedure Query for better performance

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related