|
|
Posted on 5/21/2018 1:55:48 PM
|
|
|
In the past two days, I have encountered a problem that is quite frustrating, a stored procedure of about 120 lines is executed in the query analyzer of SQL Server 2012,
The speed is very ideal, and about 500 data records can be filtered and captured in less than 1 second.
However, when called in the C# program code, the connection timeout is prompted. Set the CommandTimeout to 300, and it will take about 3 minutes to display.
I checked the code several times and found no errors. The problem remains.
Cause analysis:
1. Because the execution speed in the query analyzer is fast and the amount of data is not large.
2. Only call in the program is slow.
3. Set the CommandTimeout parameter, and the result can be displayed, but it will take a long time.
In summary, it is preliminarily concluded that the problem lies in the C# code. But there was no gain after the inspection.
Check this information on Baidu.
Finally found a similar information post on the CSDN forum. One of the netizens said in the reply, "It is possible that the implementation plan has expired",
It really woke me up in my dream.
Perform immediately on the query analyzer:
Testing the program again, this time it finally worked. The speed is very satisfactory.
Cause analysis:
Since stored procedures are precompiled, an execution plan is generated on the first execution, and this execution plan is used on subsequent executions (unless the stored procedure is specified to be recompiled), rather than generating an execution plan every time it is executed.
When the object structure involved in the stored procedure is adjusted, or the related data changes significantly, which may cause the original plan to be unsuitable for the current status quo (execution plan expiration), the stored procedure should be recompiled.
Finally, attach the code for the stored procedure:
|
Previous:Share a single APP promotional page code source, adaptive, very beautiful and generous!Next:C# Open Source Framework (Reprint)
|
