CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Developer
Developer

Reputation: 487

Move the condition out of JOIN as query slow

I have the below query that matches records based on CategoryId in Booking and SMSConfiguration.

Here I need to find the records for NULL CategoryId in SMSConfiguration also. I get my expected output. But the query runs 2 mins.

If I remove the additional condition OR SMS.CategoryId IS NULL in JOIN it runs faster and less than 1 second. I am not sure how to move this out of JOIN. 

SELECT SMS.Id AS ConfigId
    ,B.BookingId AS BookingId
    ,B.StartTime AS BookingStartTime
    ,B.EndTime AS BookingEndTime
    ,B.BookingDate AS BookingDate
    ,B.Price AS Price

FROM Booking B
INNER JOIN SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId
    OR SMS.CategoryId IS NULL -- CAUSING SLOW
    AND SMS.TenantId = B.TenantId

EDIT:

Full Query

DECLARE @CurrentDateTime SMALLDATETIME

SET @CurrentDateTime='2016-08-11 08:00:00.247'

SELECT SMS.Id AS ConfigId
        ,B.BookingId AS BookingId
        ,B.StartTime AS BookingStartTime
        ,B.EndTime AS BookingEndTime
        ,B.BookingDate AS BookingDate
        ,B.Price AS Price
        ,C.CategoryName AS CategoryName
        ,PER.PersonId AS PersonId
        ,P.PatientId AS PatientId
        ,PER.FirstName AS PatientFirstName
        ,PER.LastName AS PatientLastName
        ,PER.MobileNumber AS PatientMobileNumber
        ,RP.FirstName AS DoctorFirstName
        ,RP.LastName AS DoctorLastName
        ,SMS.SMSText
        ,B.TenantId AS TenantId
    FROM Booking B
    INNER JOIN SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId
        OR SMS.CategoryId IS NULL
        AND SMS.TenantId = B.TenantId
    INNER JOIN Tenant T ON T.TenantId = B.TenantId --AND T.IsSMSEnabled=1         
    INNER JOIN Patient P ON B.PatientId = P.PatientId
    INNER JOIN Person PER ON P.PersonId = PER.PersonId
    INNER JOIN Person RP ON RP.PersonId = B.ResponsiblePersonId
    LEFT JOIN Category C ON C.CategoryId = B.CategoryId
    WHERE 
            PER.MobileNumber IS NOT NULL
            AND PER.MobileNumber <> ''

        AND (
                (
                   (
                    DATEDIFF(DAY, @CurrentDateTime, B.StartTime) = SMS.Duration
                    AND SMS.DurationType = 1
                    )
                OR (
                    DATEDIFF(DAY, @CurrentDateTime, B.StartTime) = SMS.Duration * 7
                    AND SMS.DurationType = 2
                    )
                OR (
                    DATEDIFF(DAY, @CurrentDateTime, B.StartTime) = SMS.Duration * 30
                    AND SMS.DurationType = 3
                    )
                )
               AND BeforeAfter = 0

        OR (
            (
                (
                    DATEDIFF(DAY, B.StartTime, @CurrentDateTime) = SMS.Duration
                    AND SMS.DurationType = 1
                    )
                OR (
                    DATEDIFF(DAY, @CurrentDateTime, B.StartTime) = SMS.Duration * 7
                    AND SMS.DurationType = 2
                    )
                OR (
                    DATEDIFF(DAY, @CurrentDateTime, B.StartTime) = SMS.Duration * 30
                    AND SMS.DurationType = 3
                    )
                )
            AND BeforeAfter = 1
            )
            )

Slow Plan

enter image description here

Fast Plan when I commented that OR SMS.CategoryId IS NULL

enter image description here

Upvotes: 0

Views: 50

Answers (2)

HLGEM
HLGEM

Reputation: 96600

OR is almost always slow. Usually it is far faster to use a UNION all query instead. However it is not clear to me exactly what the conditions on the join are supposed to be. Do you mean:

 INNER JOIN SMSConfiguration SMS ON (SMS.CategoryId = B.CategoryId
        OR SMS.CategoryId IS NULL)
        AND SMS.TenantId = B.TenantId

Or

 INNER JOIN SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId
        OR (SMS.CategoryId IS NULL
        AND SMS.TenantId = B.TenantId)

the first is what you are getting, however even if you mean that , you should use () to make it clear when someone goes to maintain this.

Upvotes: 1

singhsac
singhsac

Reputation: 401

Put SMS.CategoryId and B.CategoryId in WHERE condition and then check for null - its more readable that way. Try running the following query, it reduces the table iteration cycle by 1

SELECT
    SMS.Id AS ConfigId
    ,B.BookingId AS BookingId
    ,B.StartTime AS BookingStartTime
    ,B.EndTime AS BookingEndTime
    ,B.BookingDate AS BookingDate
    ,B.Price AS Price

FROM
    Booking B
    INNER JOIN SMSConfiguration SMS
        ON SMS.TenantId = B.TenantId
WHERE
    B.CategoryId = ISNULL(SMS.CategoryId,B.CategoryId)

Upvotes: 1

Related Questions