Sqlserver: Performance Diagnosis case: In the stored procedure, select the result set for the same table and then delete it, resulting in performance impact!

For example:

Create procedure test_proc

As

Begin

Set nocount on

Declare @ ID int

Select @ ID = ID from test_table where left (C1, 3) = 'xxx'

Select * From test_table where id = @ ID

Delete from test_table where id = @ ID

End

 

The data volume in the table is small. There are about a thousand entries, and the ID and C1 columns are indexed.

In this example, if you simply use the query analyzer to execute the task, you can view the execution plan without any exception.

However, for concurrent client execution, a problem occurs: the execution time of the stored procedure is long and long, but it is not a deadlock!

This is a problem encountered in actual production. after a long time of troubleshooting, the Select result set is saved to the temporary table by introducing a temporary table, finally, the temporary table result set is returned at the end of the stored procedure to solve the problem.

 

 

The internal mechanism of the Final Cause is unknown. continue to pay attention to it .............