Hi,
We've been having an issue with a Sales Order where every time we try to delete a Sales Order Detail item it takes 1-2 minutes for each item.
I think I've traced the issue using SQL Profiler and I think it's because of expected behaviour in CRM 4.0 but I'm not sure what to do about it.
Basically this order has 8000 line items and takes 1-2 mins to delete each item whereas on an order with only a few hundred line items it only takes a few seconds to delete an item.
I fired up SQL Profiler and did some testing and it seems that the process for each deletion is that the record is marked for deletion and then CRM proceeds to check each of the remaining line items on the Order (2 selects) to make sure it's pricing details
are fine and then updates the Order's pricingerrorcode flag with the relevant status depending on what it finds.
Now the issue with this is that as our order has 8000 items, if we need to delete say 10 line items there are: (7998 + 7996 + 7994 + 7992 + 7990 + 7988 + 7986 + 7984 +7982 + 7980) x 2 = 159780
selects carried out during the deletions which takes 10-20 mins at best.
Of course what should happen is that the 10 records should be marked for deletion and then the order pricing status is checked so that there's only the (7998 x 2) = 15996 selects carried out which should then only take a minute or 2 regardless of how many
deletes there are.
Now this CRM server is not using the latest update for CRM 4.0 (I know, please don't shout) which I'm not in a position to change at the moment although perhaps I may be able to do this sometime soon.
So basically I'm wondering if anyone can confirm that my findings would be correct behaviour for CRM and if there has been a roll-up at some point that may have fixed this behaviour so that when doing bulk deletes it does the deletions first and then updates
the sales order entity's pricingerrorcode?
Thanks,
Ian