Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap