Re: Stored Proc Recompile

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Sun, 10 May 2009 13:08:15 -0700, Brian Brian wrote:

Hi,

I have a question and hope someone can help answer. We are using SQL2005
SP2 64bit Ent on Win2003 Ent 64bit. And our application is written in Java.
In order to promote re-use of query plans, our java application is using
prepared statements. I captured following in the profiler:

declare @p1 int
set @p1=115
exec sp_prepexec @p1 output,N'@p1 int',N'execute poc_getPlanDetails @p1'
,534291
select @p1

This is basically a prepared execution of SP instead of calling the SP
directly. My question is that, since it is being prepared every time, will
this SP be compiled every time as well?

Thanks very much.

Hi Brian,

This method of invoking the stored procedure should not trigger a
recompile for the procedure itself.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages