locked
How do i make the below query Sargable ? RRS feed

  • Question

  • HI all,

    Please help me in making the below query sargable :

      SELECT    
        CB.Booking_id,    
        C.ProductCode,    
        C.DestinationCode,    
        S.BirthDate, 
        MIN(CB.StartWeekCode) StartWeekCode,    
        MAX(CB.EndWeekCode) EndWeekCode,    
        SUM(CB.Weeks) TotalCourseWeeks    
       INTO #CourseDetails    
       FROM Booking B(NOLOCK)    
       INNER JOIN CourseBooking CB(NOLOCK)     
        ON CB.Booking_id = B.Booking_id    
        AND B.StatusCode = 'AC'    
        AND CB.StatusCode IN ('AC', 'CF')    
        AND (B.PoseidonGroupCode = @GroupCode2 OR @GroupCode2 = '')    
       INNER JOIN Course C(NOLOCK)     
        ON C.Course_id = CB.Course_id         
      
       INNER JOIN Student S (NOLOCK)    
        ON S.Student_id = B.Student_id    
       INNER JOIN #tmpDestination td     
        ON td.DestinationCode = C.DestinationCode    
       INNER JOIN #tmpProduct tp     
        ON tp.ProductCode = C.ProductCode    
       INNER JOIN #tmpProgram tprgm     
        ON tprgm.ProgramCode = C.ProgramCode    
        WHERE    C.CourseTypeCode IN    
         (    
          CASE    
           WHEN @CourseCodes IS NULL THEN    
            C.CourseTypeCode    
           ELSE    
            @CourseCodes    
          END    
         )    
       GROUP BY CB.Booking_id,    
        C.ProductCode,    
        C.DestinationCode,    
        S.BirthDate    
       HAVING (MIN(CB.StartWeekCode) >= @CourseStartWeekFrom2 OR ISNULL(@CourseStartWeekFrom2, '') = '')    
        AND (MIN(CB.StartWeekCode) <= @CourseStartWeekTo2 OR ISNULL(@CourseStartWeekTo2, '') = '')    
        AND (MAX(CB.EndWeekCode) >= @CourseEndWeekFrom2 OR ISNULL(@CourseEndWeekFrom2, '') = '')    
        AND (MAX(CB.EndWeekCode) <= @CourseEndWeekTo2 OR ISNULL(@CourseEndWeekTo2, '') = '')    
        

    Regards

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Thursday, April 18, 2013 5:32 AM

Answers

All replies

  • How do you want us to test the query? Can you provide an execution plan of the query? What indexes do you have in place? How big are the tables? What version are  you using?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, April 18, 2013 5:39 AM
    Answerer
  • Hi Uri,

    As per my investigation, for all the tables referred in the above query, i see the index size is greater than the data size, does this has a Bad impact on the performance ?

    Actually i tried posting the execution plan xml, where i got a popup saying " body cant be more than 600000" .

    But i can get u the table size details here :

    Booking
    ---------
    Data Size : 273008 KB
    Index size :506432 KB


    CourseBooking
    --------------

    Data Size : 79808 KB
    Index size :907800 KB


    Student
    ---------
    Data Size : 245672 KB
    Index size :314632 KB

    Course
    --------
    Data Size : 43584 KB
    Index size :441792 KB

    Regards

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.


    Thursday, April 18, 2013 5:59 AM
  • The predicate logic seems to be incorrect in CASE. 

    If @couresecodes is NULL, then the resulting predicate is 'C.CourseTypeCode=C.CourseTypeCode', which makes the query non-sargable.

    Please have a look at, http://blog.wharton.com.au/2011/06/18/sargable-predicates/


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Thursday, April 18, 2013 6:40 AM
  • If the column Course.CourseTypeCode is never NULL, and @CourseCodes will never contain special characters like %, _ or [, then you could replace

    WHERE    C.CourseTypeCode IN    
         (    
          CASE    
           WHEN @CourseCodes IS NULL THEN    
            C.CourseTypeCode    
           ELSE    
            @CourseCodes    
          END    
         )    

    with

    WHERE    C.CourseTypeCode LIKE COALESCE(@CourseCodes, '%')
    This change would make this predicate sargable.


    Gert-Jan


    Thursday, April 18, 2013 8:41 PM
  • I think you meant to replace with

    WHERE    C.CourseTypeCode LIKE COALESCE(@CourseCodes, '%')

    I would go with dynamic SQL solution here as there are several criteria.

    See

    Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
    Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
    Catch All Queries - short blog by Gail Shaw
    Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

    Option recompile

    Option recompile discussion thread

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 18, 2013 9:01 PM
  • Yes, I did. Thanks for noticing. I just corrected it.

    Gert-Jan

    Thursday, April 18, 2013 9:20 PM