February 16, 2023 at 9:25 am
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
February 16, 2023 at 1:31 pm
Have you looked at the Execution Plans?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 16, 2023 at 1:52 pm
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
February 16, 2023 at 3:55 pm
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
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 16, 2023 at 5:13 pm
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)
February 16, 2023 at 6:56 pm
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