SQL optimizations in ERP that are too slow for approval

In the implementation of ERP to customers, often encountered customer purchase requisition approval, particularly slow, a stage to turn 40, 50 seconds or so, 3 stages of almost 3 minutes, the efficiency is very low.

The check method is as follows:

1, the database tracking statements, find the execution time of the statement, the results are as follows:

2, as can be seen, the execution of the statement, the time is almost 45 seconds (Drration Unit is MS), this paragraph is copied out, for analysis;

3, WFWDD (Approval) and Wfwdda table data, especially the Wfwdda table has 20 tens of thousands of of the data, which is the main cause of slow query;

Solution:

Create indexes for these two tables to speed up query efficiency, now provide the statement to create the index, the statement is as follows:

1 CREATE nonclustered INDEX [Ix_draftkey]  on [dbo].[Wfwdd] 2 (3 [Draftkey] ASC4 )5 CREATE nonclustered INDEX [Ix_workflowinstanceid]  on [dbo].[Wfwdda] 6 (7 [Workflowinstanceid] ASC8)

1, encountered similar approval too slow, can refer to;

2, reasonable use of SQL statement tracking tool to find the root cause of the problem.

Tool usage instructions see: Simple use of SQL Server Profiler query tracking

Index Introduction SEE: Index Introduction

SQL optimizations in ERP that are too slow for approval