we are trying to calculate the Planning Worksheet.
We notice when the "Send Ahead Quantity" is greater than zero then the calculate takes a long time.
I run with SQL Server Profiler and detect that
DELETE FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need" WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3)) as a duration bigger than the other statements.
I run the client Monitor and see the statement DELETEALL on CodeUnit 99000809.
Ok, but that would mean that the DELETEALL takes longer because there are more lines
to delete (thus created before) if "Send Ahead Quantity" is greater than 0. This would be a logical problem.
Technically, there's no relation - the filter of the DELETEALL statement does not refer to "Send Ahead Quantity".
So could you determine how many lines are deleted then? Maybe it's plausible to have such a number of "Reads" and "Duration" ...
CPU - 187
Reads - 66820
Duration - 186
Have in mind that a DELETE also implicitly modifies all kinds of related statistics and indexes, but also the VSIFT (if existing).
Sufficient DB Maintenance like Index Defragmentation and Statistic Updates should be obligatory anyway, but I'm not sure if that will solve the problem ...
Actually I doubt that this DELETE is the core of the problem: you say the whole process takes about 2 minutes, the DELETE only takes 186 milliseconds ... (actually Kriki already highlighted that) ...