AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, January 02, 2014

"with recompile" SP option

In addition to my recent post Running SP with Access pass-through query. set nocount on did the trick, but only for one day. My next try was to add with recompile option to store procedure declaration, and after 2 weeks I must admit this was a solution!

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)
with recompile
as

Labels:

3 Comments:

Anonymous Anonymous said...

As your database has grown over time, the original query plan is no longer optimal. Thus the decrease in the first place.

See also this TechNet articel about Recompile:
http://technet.microsoft.com/en-us/library/ms190439.aspx

But the basic problem is that the optimizer can not always choose the optimal plan due to dynamic SQL.
See Adam's article about it: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx

7:18 PM  
Blogger Alex Dybenko said...

Yes, this is what i found. Strange, that it worked more than 2 years without problem...

10:37 AM  
Anonymous tim said...

hmm jepp quite strange.. any ideas why?

6:54 PM  

Post a Comment

<< Home