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```