Coalesce performance issue

  • Hi,

    i've an ERP program that i can't change anything in query that comes to the SQL Server.

    i've SQL 2019 STD FULLY patched and i have problem with this query that i think i caused by the coalesce function.

    can someone explain me please why if i remove the coalesce only from this location

    and ( ( grppro.dbo.PLNT_PARTACTWORKC.WORKC ) = PLNT_WORKC1.WORKC )

    the query finish after less then a sec and with this coalesce it finished after 8 sec?

    the full query

    exec sp_executesql N'select                  grppro.dbo.PLNT_TASKACT.TASKACT from grppro.dbo.PLNT_PARAMETERS  inner join grppro.dbo.SERIALA  on 1 = 1 inner join grppro.dbo.PART  on 1 = 1 inner join grppro.dbo.SERIAL  on ( grppro.dbo.SERIAL.SERIAL = grppro.dbo.SERIALA.SERIAL ) inner join grppro.dbo.PLNT_TASKACT  on ( grppro.dbo.PLNT_TASKACT.TASKACT > @P17 ) and ( ( grppro.dbo.PLNT_TASKACT.TASK <> @P18 ) or ( grppro.dbo.PLNT_TASKACT.POS <> @P19 ) ) and ( grppro.dbo.PLNT_TASKACT.TASKACT <> @P20 ) inner join grppro.dbo.ORDERS  on 1 = 1 inner join grppro.dbo.ORDERITEMS  on ( grppro.dbo.ORDERITEMS.ORD = grppro.dbo.ORDERS.ORD ) inner join grppro.dbo.ACT  on ( grppro.dbo.ACT.ACT = grppro.dbo.PLNT_TASKACT.ACTORIG ) inner join grppro.dbo.WORKC  on ( grppro.dbo.WORKC.WORKC = grppro.dbo.ACT.WORKC ) inner join grppro.dbo.PARTPARAM  on ( grppro.dbo.PARTPARAM.PART = grppro.dbo.PART.PART ) inner join grppro.dbo.PLNT_TASKS  on ( grppro.dbo.PLNT_TASKS.TASK = grppro.dbo.PLNT_TASKACT.TASK ) inner join grppro.dbo.WORKC WORKC1 on ( WORKC1.WORKC = grppro.dbo.PLNT_TASKACT.REPWORKC ) inner join grppro.dbo.DEPT  on ( grppro.dbo.DEPT.DEPT = grppro.dbo.WORKC.DEPT ) inner join grppro.dbo.PARTSPEC  on ( grppro.dbo.PARTSPEC.PART = grppro.dbo.PART.PART ) inner join grppro.dbo.PART PART1 on ( PART1.PART = grppro.dbo.PLNT_TASKACT.PARTKNIFE ) inner join grppro.dbo.PART PART2 on ( PART2.PART = grppro.dbo.PLNT_TASKACT.PARTSHEET ) inner join grppro.dbo.CUSTOMERS  on ( grppro.dbo.CUSTOMERS.CUST = grppro.dbo.ORDERS.CUST ) inner join grppro.dbo.PART PART3 on ( PART3.PART = grppro.dbo.PLNT_TASKACT.PARTONESIDE ) inner join grppro.dbo.SERIALSTATUS  on ( grppro.dbo.SERIALSTATUS.SERIALSTATUS = grppro.dbo.SERIALA.SERIALSTATUS ) inner join grppro.dbo.SERIAL SERIAL1 on ( SERIAL1.SERIAL = grppro.dbo.SERIAL.ZGRP_MSERIAL ) inner join grppro.dbo.PART PART4 on ( PART4.PART = grppro.dbo.PLNT_TASKACT.PARTLAQA ) inner join grppro.dbo.PROCESS  on ( grppro.dbo.PROCESS.T$PROC = grppro.dbo.PLNT_TASKS.T$PROC ) inner join grppro.dbo.FAMILY  on ( grppro.dbo.FAMILY.FAMILY = grppro.dbo.PLNT_TASKS.COLORFAMILY ) inner join grppro.dbo.WORKC WORKC2 on 1 = 1 inner join grppro.dbo.PLNT_WORKC PLNT_WORKC1 on 1 = 1 left outer join grppro.dbo.PLNT_PARTACTWORKC  on ( grppro.dbo.PLNT_PARTACTWORKC.ACT = grppro.dbo.ACT.ACT ) and ( grppro.dbo.PLNT_PARTACTWORKC.PART = grppro.dbo.PART.PART ) left outer join grppro.dbo.PLNT_WORKC  on ( grppro.dbo.PLNT_WORKC.WORKC = grppro.dbo.WORKC.WORKC )  where grppro.dbo.WORKC.DEPT in ( select                  grppro.dbo.PLNT_USERDEPT.DEPT  from grppro.dbo.PLNT_USERDEPT   where ( grppro.dbo.PLNT_USERDEPT.T$USER = @P21 ) ) and ( coalesce( grppro.dbo.PLNT_PARTACTWORKC.WORKC , 0 ) = WORKC2.WORKC ) and ( coalesce( grppro.dbo.PLNT_PARTACTWORKC.WORKC , 0 ) = PLNT_WORKC1.WORKC ) and ( ( grppro.dbo.SERIAL.CLOSED <> N''C'' ) or ( ( grppro.dbo.SERIAL.CLOSEDATE > ( @P22 - ( convert(decimal(13,2), grppro.dbo.PLNT_PARAMETERS.NUMERICVAL) * @P23 ) ) ) and ( grppro.dbo.SERIALSTATUS.MANUALCLOSED = N''Y'' ) ) ) and ( grppro.dbo.PLNT_TASKS.PART = grppro.dbo.PART.PART ) and ( grppro.dbo.PLNT_TASKS.SERIAL = grppro.dbo.SERIAL.SERIAL ) and ( grppro.dbo.PLNT_TASKS.CLOSED <> N''Y'' ) and ( grppro.dbo.PLNT_TASKS.ORDI = grppro.dbo.ORDERITEMS.ORDI ) and ( grppro.dbo.PLNT_PARAMETERS.PARAMNAME = @P24 ) and ( @P25 = @P26 )  order by 1     ',N'@P1 bigint,@P2 bigint,@P3 nvarchar(1),@P4 bigint,@P5 bigint,@P6 bigint,@P7 bigint,@P8 bigint,@P9 bigint,@P10 bigint,@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint,@P16 bigint,@P17 bigint,@P18 bigint,@P19 bigint,@P20 bigint,@P21 bigint,@P22 bigint,@P23 bigint,@P24 nvarchar(15),@P25 bigint,@P26 bigint,@P27 bigint,@P28 bigint',1,0,N' ',0,0,0,0,100,1440,0,18475200,7,60,1,1440,0,0,0,0,0,73,18475840,1440,N'WOCloseHideDays',1,1,0,6000

    THX

  • Have you looked at the Execution Plans?

    • This reply was modified 1 year, 10 months ago by  Phil Parkin.
    • This reply was modified 1 year, 10 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • It's simple. COALESCE as part of filtering criteria, WHERE/ON/HAVING, means that statistics and indexes can't be used becuase you're making a calculation. That's going to slow things down, leading to scans. You need to change the logic to something else, an OR, another join, something that can use statistics and indexes to help performance.

    "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

  • For every else's sanity:

    SELECT grppro.dbo.PLNT_TASKACT.TASKACT
    FROM grppro.dbo.PLNT_PARAMETERS
    INNER JOIN grppro.dbo.SERIALA ON 1 = 1
    INNER JOIN grppro.dbo.PART ON 1 = 1
    INNER JOIN grppro.dbo.SERIAL ON (grppro.dbo.SERIAL.SERIAL = grppro.dbo.SERIALA.SERIAL)
    INNER JOIN grppro.dbo.PLNT_TASKACT ON (grppro.dbo.PLNT_TASKACT.TASKACT > @P17)
    AND ((grppro.dbo.PLNT_TASKACT.TASK <> @P18)
    OR (grppro.dbo.PLNT_TASKACT.POS <> @P19))
    AND (grppro.dbo.PLNT_TASKACT.TASKACT <> @P20)
    INNER JOIN grppro.dbo.ORDERS ON 1 = 1
    INNER JOIN grppro.dbo.ORDERITEMS ON (grppro.dbo.ORDERITEMS.ORD = grppro.dbo.ORDERS.ORD)
    INNER JOIN grppro.dbo.ACT ON (grppro.dbo.ACT.ACT = grppro.dbo.PLNT_TASKACT.ACTORIG)
    INNER JOIN grppro.dbo.WORKC ON (grppro.dbo.WORKC.WORKC = grppro.dbo.ACT.WORKC)
    INNER JOIN grppro.dbo.PARTPARAM ON (grppro.dbo.PARTPARAM.PART = grppro.dbo.PART.PART)
    INNER JOIN grppro.dbo.PLNT_TASKS ON (grppro.dbo.PLNT_TASKS.TASK = grppro.dbo.PLNT_TASKACT.TASK)
    INNER JOIN grppro.dbo.WORKC WORKC1 ON (WORKC1.WORKC = grppro.dbo.PLNT_TASKACT.REPWORKC)
    INNER JOIN grppro.dbo.DEPT ON (grppro.dbo.DEPT.DEPT = grppro.dbo.WORKC.DEPT)
    INNER JOIN grppro.dbo.PARTSPEC ON (grppro.dbo.PARTSPEC.PART = grppro.dbo.PART.PART)
    INNER JOIN grppro.dbo.PART PART1 ON (PART1.PART = grppro.dbo.PLNT_TASKACT.PARTKNIFE)
    INNER JOIN grppro.dbo.PART PART2 ON (PART2.PART = grppro.dbo.PLNT_TASKACT.PARTSHEET)
    INNER JOIN grppro.dbo.CUSTOMERS ON (grppro.dbo.CUSTOMERS.CUST = grppro.dbo.ORDERS.CUST)
    INNER JOIN grppro.dbo.PART PART3 ON (PART3.PART = grppro.dbo.PLNT_TASKACT.PARTONESIDE)
    INNER JOIN grppro.dbo.SERIALSTATUS ON (grppro.dbo.SERIALSTATUS.SERIALSTATUS = grppro.dbo.SERIALA.SERIALSTATUS)
    INNER JOIN grppro.dbo.SERIAL SERIAL1 ON (SERIAL1.SERIAL = grppro.dbo.SERIAL.ZGRP_MSERIAL)
    INNER JOIN grppro.dbo.PART PART4 ON (PART4.PART = grppro.dbo.PLNT_TASKACT.PARTLAQA)
    INNER JOIN grppro.dbo.PROCESS ON (grppro.dbo.PROCESS.T$PROC = grppro.dbo.PLNT_TASKS.T$PROC)
    INNER JOIN grppro.dbo.FAMILY ON (grppro.dbo.FAMILY.FAMILY = grppro.dbo.PLNT_TASKS.COLORFAMILY)
    INNER JOIN grppro.dbo.WORKC WORKC2 ON 1 = 1
    INNER JOIN grppro.dbo.PLNT_WORKC PLNT_WORKC1 ON 1 = 1
    LEFT OUTER JOIN grppro.dbo.PLNT_PARTACTWORKC ON (grppro.dbo.PLNT_PARTACTWORKC.ACT = grppro.dbo.ACT.ACT)
    AND (grppro.dbo.PLNT_PARTACTWORKC.PART = grppro.dbo.PART.PART)
    LEFT OUTER JOIN grppro.dbo.PLNT_WORKC ON (grppro.dbo.PLNT_WORKC.WORKC = grppro.dbo.WORKC.WORKC)
    WHERE grppro.dbo.WORKC.DEPT IN (SELECT grppro.dbo.PLNT_USERDEPT.DEPT
    FROM grppro.dbo.PLNT_USERDEPT
    WHERE (grppro.dbo.PLNT_USERDEPT.T$USER = @P21))
    AND (COALESCE(grppro.dbo.PLNT_PARTACTWORKC.WORKC, 0) = WORKC2.WORKC)
    AND (COALESCE(grppro.dbo.PLNT_PARTACTWORKC.WORKC, 0) = PLNT_WORKC1.WORKC)
    AND ((grppro.dbo.SERIAL.CLOSED <> N'C')
    OR ((grppro.dbo.SERIAL.CLOSEDATE > (@P22 - (CONVERT(decimal(13, 2), grppro.dbo.PLNT_PARAMETERS.NUMERICVAL) * @P23)))
    AND (grppro.dbo.SERIALSTATUS.MANUALCLOSED = N'Y')))
    AND (grppro.dbo.PLNT_TASKS.PART = grppro.dbo.PART.PART)
    AND (grppro.dbo.PLNT_TASKS.SERIAL = grppro.dbo.SERIAL.SERIAL)
    AND (grppro.dbo.PLNT_TASKS.CLOSED <> N'Y')
    AND (grppro.dbo.PLNT_TASKS.ORDI = grppro.dbo.ORDERITEMS.ORDI)
    AND (grppro.dbo.PLNT_PARAMETERS.PARAMNAME = @P24)
    AND (@P25 = @P26)
    ORDER BY 1;

    Just because it's a defered statement doesn't mean you shouldn't use formatting. Formatting is just as important, regardless of if you are using a deffered statement, or not.

    Also, 3+ naming for columns is to be deprecated; you're using 4 part naming here. All your objects are also all in the same database, grppro so why are you using 3 part naming in the FROM? Why not just connect to the database grppro and then use 2 part naming for your objects, alias them, and then use the alias to qualify your columns? You'll find the SQL is far more readable that way.

    INNER JOIN ... ON 1 = 1 is an odd choice as well. Why not just use a CROSS JOIN?

    In regards to the COALESCE I suspect that you shouldn't be using them at all. The column you use the functio on is from the PLNT_PARTACTWORKC table, which you LEFT JOIN to. More likely you should just have the clause PP.WORKC = WORKC2.WORKC in the ON clause of the LEFT JOIN.

    I also note the clause where you CONVERT the value of NUMERICVAL to a decimal; what data type is that column? That's also probably going to make the query non-SARGable. The arithemtical on the column in the WHERE will also make it non-SARGable, but you also compare that value to one called CLOSEDATE? Dates aren't numbers, so this has a smell too; is this resulting in a further implicit conversion to a datetime?

    Also, some related reading on your ORDER BY: Bad Habits to Kick : ORDER BY ordinal

    • This reply was modified 1 year, 10 months ago by  Thom A. Reason: Mention CROSS JOIN

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Does the row count change when you remove the coalesce? I think the coalesce is there because dbo.PLNT_PARTACTWORKC is joined with an outer join. I suspect that removing the coalesce turns it into an inner join, because a null PLNT_PARTACTWORKC.WORKC  won't equal WORKC2.WORKC or PLNT_WORKC1.WORKC. I think Grant explained why it affects performance, but without it I think the results will change. I assume you can't tell the vendor to rewrite the code.

    LEFT OUTER JOIN grppro.dbo.PLNT_PARTACTWORKC 
    ON PLNT_PARTACTWORKC.ACT = ACT.ACT AND PLNT_PARTACTWORKC.PART = PART.PART
    WHERE ...
    AND (COALESCE(PLNT_PARTACTWORKC.WORKC, 0) = WORKC2.WORKC)
    AND (COALESCE(PLNT_PARTACTWORKC.WORKC, 0) = PLNT_WORKC1.WORKC)

     

  • You're doing (the equivalent of) a CROSS JOIN on the WORKC and PLNT_WORKC tables (JOIN ON 1 = 1).  That could generate a huge number of rows.  You then filter out rows in the WHERE clause.  It should be more efficient to specify the join condition in the JOINs themselves:

    INNER JOIN grppro.dbo.PROCESS ON (grppro.dbo.PROCESS.T$PROC = grppro.dbo.PLNT_TASKS.T$PROC)

    INNER JOIN grppro.dbo.FAMILY ON (grppro.dbo.FAMILY.FAMILY = grppro.dbo.PLNT_TASKS.COLORFAMILY)

    --this join moves up, so that it is before the next two joins

    LEFT OUTER JOIN grppro.dbo.PLNT_PARTACTWORKC ON (grppro.dbo.PLNT_PARTACTWORKC.ACT = grppro.dbo.ACT.ACT)

    AND (grppro.dbo.PLNT_PARTACTWORKC.PART = grppro.dbo.PART.PART)

    INNER JOIN grppro.dbo.WORKC WORKC2 ON (COALESCE(grppro.dbo.PLNT_PARTACTWORKC.WORKC, 0) = WORKC2.WORKC)

    INNER JOIN grppro.dbo.PLNT_WORKC PLNT_WORKC1 ON (COALESCE(grppro.dbo.PLNT_PARTACTWORKC.WORKC, 0) = PLNT_WORKC1.WORKC)

    LEFT OUTER JOIN grppro.dbo.PLNT_WORKC ON (grppro.dbo.PLNT_WORKC.WORKC = grppro.dbo.WORKC.WORKC)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

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