Performance Question

Question

Performance Question

ah0996

SSC Veteran

Points: 212
More actions
May 10, 2018 at 10:53 am

#330806

Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

ah0996 - Thursday, May 10, 2018 10:53 AM
Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced

Can't actually tell without the actual execution plan but my bet would be on the RIGHT() function, which makes the JOIN non-sargable and would probably result in the lazy spool along with the use of OR in a join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

ah0996 - Thursday, May 10, 2018 10:53 AM
Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced

You have a function around a column in the JOIN clause. That is a recipe for horrible performance for several reasons. An OR clause can present issues for the optimizer as well.

How about table create scripts (with indexing too), row counts, and the (preferably actual) query plan.

Have you tried this as two different queries instead of combining the OR JOIN predicate?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Not to mention NOLOCK everywhere, SELECT *... This is just a series of common code anti-patterns.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sergiy SSC Guru Points: 110208 More actions · Answer 4

ah0996 - Thursday, May 10, 2018 10:53 AM
Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced

Try this:

        LEFT OUTER JOIN dbo.USOFRTA USOFRTA
            ON EDI94X_Error.OrderNumber =EDI94X_Error.OrderNumber =
            CASE WHEN USOFRTA.sicred > '20200201' THEN USOFRTA.sicred > '20200201' THEN USOFRTA.SIUSORNA ELSE USOFRTA.SIUSORNA ELSE right(USOFRTA.SIUSORNA,7) END

But check first that my assumption about the data logic is correct.

_____________
Code for TallyGenerator

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Fixing your data model to avoid this type of tomfoolery is always my first choice.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

michael.cole 47030 SSC Eights! Points: 804 More actions · Answer 6

ah0996 - Thursday, May 10, 2018 10:53 AM
Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced

Create a persisted computed column on the USOFRTA table, to be CASE WHEN sicred > '20200201' THEN SIUSORNA ELSE right(SIUSORNA,7) END
Stick an index on it. Join onto that computed column.
Note that this doesn't exactly match what the clause does, but the clause does look kind of weird. The question is, when the sicred is greated than 20200201, can it only match on the full SIUSORNA or can it also match on only the last seven characters of the SIUSORNA?

Budd Hall of Fame Points: 3781 More actions · Answer 7

Hi,
I have much more to learn than to teach, but I have a habit of using a CTE when I need to JOIN or WHERE on a function.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Budd - Wednesday, May 16, 2018 7:35 AM
Hi,
I have much more to learn than to teach, but I have a habit of using a CTE when I need to JOIN or WHERE on a function.

That usually doesn't help in preventing scans.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

michael.cole 47030 - Monday, May 14, 2018 11:18 PM
ah0996 - Thursday, May 10, 2018 10:53 AM
Hi there,
I have a performance question here:
how can I rewrite below highlighted area? I am getting Lazy spool in execuation plan for highlighted area and not sure how to fix it..
SELECT *
FROM
.dbo.EDI94X_Error EDI94X_Error (NOLOCK)
        LEFT OUTER JOIN dbo.XRORFL XRORFL (NOLOCK)
             ON EDI94X_Error.OrderNumber=XRORFL.XRLORNO
        LEFT OUTER JOIN dbo.USOFRTA USOFRTA (NOLOCK)
            ON     (EDI94X_Error.OrderNumber=right(USOFRTA.SIUSORNA,7)
                OR (EDI94X_Error.OrderNumber=USOFRTA.SIUSORNA AND USOFRTA.sicred > '20200201'))
Thank you in advanced
Create a persisted computed column on the USOFRTA table, to be CASE WHEN sicred > '20200201' THEN SIUSORNA ELSE right(SIUSORNA,7) END
Stick an index on it. Join onto that computed column.
Note that this doesn't exactly match what the clause does, but the clause does look kind of weird. The question is, when the sicred is greated than 20200201, can it only match on the full SIUSORNA or can it also match on only the last seven characters of the SIUSORNA?

Actually, just create a persisted computed column with the function RIGHT(SIUSORNA,7) and then index that column.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT * -- select only the columns you need, maximise chance of ordinary index being chosen over CI scan

FROM dbo.EDI94X_Error e

LEFT JOIN dbo.XRORFL x

ON e.OrderNumber = x.XRLORNO

LEFT JOIN dbo.USOFRTA u1

ON e.OrderNumber = RIGHT(u1.SIUSORNA,7) -- not SARGable

-- joining the same table twice is often faster than using OR

LEFT JOIN dbo.USOFRTA u2

ON e.OrderNumber = u2.SIUSORNA AND u2.sicred > '20200201'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden