[Original] SQL stored procedure calling is slow, but re-Compilation of the stored procedure is very fast.

When. Net calls a stored procedure, it may be slow or even timeout, but the execution of the stored procedure will be very fast.

 

Solution:

1. Add option (recompile) to statements that may be time-consuming)

2. When creating a stored procedure, specify the with recompile option in its definition, indicating that SQL server will not cache the Stored Procedure plan at high speed; the stored procedure will be re-compiled upon each execution. The with recompile option can be used when the parameter values of a stored procedure vary greatly between different executions, resulting in different execution plans to be created each time. This option is not commonly used because it must be re-compiled every time a stored procedure is executed, which slows down the execution of the stored procedure.

You can specify the with recompile option when executing a stored procedure to forcibly recompile the stored procedure. This option should be used only when the provided parameters are not typical, or when the data changes significantly after the stored procedure is created.