Five off-bus scans to optimize the entire process

Get off scanning, business departments are slow and unstable,ProgramIf it is not yellow pages, the operation is slow, which seriously affects the use of the master. It is estimated that the master has always "missed us ".

First Optimization

I read the business logic of the program with my colleagues and thought that the entire scan logic process should be stored in the stored procedure. First, we can avoid the impact of the Program on interaction, and second, we can improve the performance.

After modification, you need to run the following commands in the stored procedure to read the sequence.
To obtain:
Select @ deliveryitemstatussysno = DBO. createsequence ('delimiteritem _ status_sequence ', 1, '192. 168.2.8', 192)

Second Optimization

 
It wasn't long before the scanning process was optimized and went live. On the morning of Saturday, my colleagues said that there was still a problem with the scanning and the operation was slow and unstable. After analysis, they found that the scanning was a sequence problem.
Because sequence cannot be used concurrently. The stored procedure can only be executed after the sequence is prefetch.
Changed:
Select @ deliveryitemstatussysno = min (sysno) from deliveryitem_status_sequence where useflag = 0
Update deliveryitem_status_sequence set useflag = 1 where sysno = @ deliveryitemstatussysno and useflag = 0
 

Third Optimization

After optimization, I thought it would not be slow to get sequence this time, but the new problem is coming again. Because of the high concurrency of this Order scanning, there will be about 6 K Orders from AM to AM.
The sequence is repeated after running. An error is returned when you repeat the request. Later, it can only be changed:
Change the isolation level to the highest level so that the isolation level will not be repeated:
SET transaction isolation level serializable
Begin tran
Select @ deliveryitemstatussysno = min (sysno) from deliveryitem_status_sequence where useflag = 0
Update deliveryitem_status_sequence set useflag = 1 where sysno = @ deliveryitemstatussysno and useflag = 0
Commit tran
SET transaction isolation level read committed
 

Fourth Optimization

 
After three optimizations, there should be no problem, but after the operation, a large number of deadlocks are found. As the isolation level is improved, concurrency is changed to sequential commit, and the deadlock is coming up. Are there any
I have been troubled by other methods to solve the problem of concurrency and repetition. I checked the help documentation of SQL Server. Think of a new method. Modify the SQL statement as follows:
 

Declare @ tbvarsysno table (
Sysno int
)
-- Update the status table
Update top (1) deliveryitem_status_sequence
Set useflag = 1
Output inserted. sysno
Into @ tbvarsysno where useflag = 0;
Select @ deliveryitemstatussysno = sysno from @ tbvarsysno

The preceding SQL statement directly modifies a random sysno and extracts it. You do not need to select or update the table twice to avoid repeated and concurrent queries due to the time difference between queries and updates.
 

Fifth Optimization

 
After the above four optimizations, it is observed that the problem of non-repetition and concurrency is solved, but the update of merge into deliveryitem_status
A deadlock occurs in the Table. After tracking the deadlock information, it is found that the serializable isolation level is used to check the program and the stored procedure,
There is no declaration of the serializable isolation level. It is estimated that it is a SQL Server bug. No way, you can only manually declare it in the Stored Procedure
Read committed isolation level ,,
 

 
After this modification,Two days after running, we found that the above deadlock, repeated sysno and slow execution had been solved.

Summary
The above is the whole process and solution of the five optimization scans. We hope to provide some information and reference for the subsequent optimization systems.