Hot fix #32 contains two important performance improvements that were fixed in Hot fix #31. I'll cover one of the improvements below, and will post another entry later discussing the second performance fix.
The first performance improvement is related to a delete statement that is run in the background and is triggered my multiple events during a user’s activity within Vision. The delete statement below was responsible for clearing out old entries in the workflow log table.
DELETE From WorkflowLog WHERE
datediff(dd,WorkflowTime,GETUTCDATE()) >
(SELECT WorkflowLogLength from CFGSystem )
Based on the execution plan below, you can see that this is a less than ideal delete statement, and the performance degradation is multiplied if you are keeping millions of rows in your workflowlog table.
As part of Hot Fix #32, Deltek re-worked the delete statement and it looks something like what is listed below.
DELETE From WorkflowLog WHERE WorkflowTime < @datevariable
Based on the execution plan below, you can see the plan is much simpler and leads to better overall performance.
If you want to squeeze out even better performance, add a non clustered index on the workflowtime column for the workflowlog table.
Enjoy
