CODEWITHSUNDEEP
sqlsql-serverleft-join
Jargo
Jargo

Reputation: 365

Slow double left join

I have the following query that performs left join between 3 tables. The problem is that it takes 20 seconds to run, even if only few rows are returned. If I leave out the "largefields" from the offer table the query executes almost instantly. So it appears that the problem is that for some reason the sqlserver fetches the values of those fields before it applies the where statement. Is there any way to make this query execute faster?

SELECT N0."OID"
    ,N1."FirstName"
    ,N1."LastName"
    ,N0."SmallField"
    ,N0."LargeField1"
    ,N0."LargeField2"
FROM (
    (
        "dbo"."Offer" N0 LEFT JOIN "dbo"."Address" N1 ON (N0."OfferDeliveryAddress" = N1."OID")
        ) LEFT JOIN "dbo"."Customer" N2 ON (N0."Customer" = N2."OID")
    )
WHERE (
        N0."GCRecord" IS NULL
        AND (
            (isnull(CharIndex('John Smith', N2."FirstName" + ' ' + N2."LastName"), 0) > 0)
            OR (isnull(CharIndex('John Smith', N2."LastName" + ' ' + N2."FirstName"), 0) > 0)
            OR (isnull(CharIndex('John Smith', N2."FirstName"), 0) > 0)
            OR (isnull(CharIndex('John Smith', N2."LastName"), 0) > 0)
            )
        )

Upvotes: 0

Views: 111

Answers (3)

cunvoas
cunvoas

Reputation: 1

  • First in the select part, removing N0."LargeField1", N."LargeField2" make a difference?

  • 2nd, in where part N2."FirstName" and N2."LastName" is potentially in trouble because querying on function didn't use the database index

  • And please check your execution plan.

Upvotes: 0

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

At least you can optimize your where condition as follow:

WHERE (
        N0."GCRecord" IS NULL
        AND (
            (isnull(CharIndex('John Smith', N2."FirstName" + ' ' + N2."LastName"), 0) > 0)
            OR (isnull(CharIndex('John Smith', N2."LastName" + ' ' + N2."FirstName"), 0) > 0)
            )
        )

because the last two conditions are included in the first two.

Edited As an example to see why this is true:

FirstName = 'XXXJohn Smith'
LastName = 'YYY'

FirstName + ' ' + LastName = 'XXXJohnSmith YYY'

so if JohnSmith is contained in FirstName it is also contained in FirstName + ' ' + LastName

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Change your WHERE clause to sargable clause:

WHERE (
        N0."GCRecord" IS NULL
        AND (
               (N2."FirstName" LIKE 'John%' AND N2."LastName" LIKE 'Smith%') OR 
               (N2."LastName" LIKE 'John%' AND N2."FirstName" LIKE 'Smith%') OR 
                N2."FirstName" LIKE 'John Smith%' OR 
                N2."LastName" LIKE 'John Smith%' 
            )
        )

Add indexes to column FirstName and LastName. This where clause should benefit from those indexes, because LIKE 'ABC%' is sargable.

Upvotes: 1

Related Questions