Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Nov 30, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Use WITH RECOMPILE Option for Dynamic Stored Procedures

When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server's buffer. If the same stored procedure is called again from the same connection, the server will use the cached query plan instead of creating a new one, saving time and boosting performance. This may or may not be what you want. If a query is dynamic (the WHERE clauses change from one execution of the stored procedure to the next), it will not be optimized when it is run, and the performance of the query can suffer greatly.

If the query will vary each time it is run from the stored procedure, add the WITH RECOMPILE option when the stored procedure is created. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time.
Andrew Holliday
Thanks for your registration, follow us on our social networks to keep up-to-date