Stored Proc Recompiling over and over

[JimBob]

I have a simple stored procedure that is recompiling over and over. I
have run several traces and I can't figure out the problem. Here is
the code:

CREATE procedure dbo.Sp_multi_selectEmployee
(
@EmployeeID int
)
As
select EmployeeID, UserName, FirstName, LastName, DealerID, Password,
tbldealeremployees.UserTypeID,
tblEmpUserTypes.Name as UserRole, DateCreated, DateLastModified,
Customized, Active, CustRoleName,
Address, Address2, SSN, EmailAddress, City, State, Zip, Country,
OwnerFlag, JobTitle, JobDesc
from dbo.tblDealerEmployees inner join dbo.tblEmpUserTypes
on dbo.tblDealerEmployees.usertypeid = dbo.tblEmpUserTypes.usertypeid
where EmployeeID = @EmployeeID

The traces report an event sub-class of 1, which according to msdn is
"Schema, bindings, or permissions changed between compile
or execute." I don't see how the permissions or
bindings have changed. I have seen a few similar posts but no
answers. I tried dropping all of the indexes on both tables but that
had no effect. I can replicate this in our development environment,
so it seems something specific to the code. Any answers, ideas?

thanks.

[Simon Hayes]

"JimBob" <ma*********@hotmail.com> wrote in message
news:de**************************@posting.google.c om...

I have a simple stored procedure that is recompiling over and over. I
have run several traces and I can't figure out the problem. Here is
the code:

CREATE procedure dbo.Sp_multi_selectEmployee
(
@EmployeeID int
)
As
select EmployeeID, UserName, FirstName, LastName, DealerID, Password,
tbldealeremployees.UserTypeID,
tblEmpUserTypes.Name as UserRole, DateCreated, DateLastModified,
Customized, Active, CustRoleName,
Address, Address2, SSN, EmailAddress, City, State, Zip, Country,
OwnerFlag, JobTitle, JobDesc
from dbo.tblDealerEmployees inner join dbo.tblEmpUserTypes
on dbo.tblDealerEmployees.usertypeid = dbo.tblEmpUserTypes.usertypeid
where EmployeeID = @EmployeeID

The traces report an event sub-class of 1, which according to msdn is
"Schema, bindings, or permissions changed between compile
or execute." I don't see how the permissions or
bindings have changed. I have seen a few similar posts but no
answers. I tried dropping all of the indexes on both tables but that
had no effect. I can replicate this in our development environment,
so it seems something specific to the code. Any answers, ideas?

thanks.

As a complete guess, try renaming the procedure so it doesn't start with
"sp_" - that prefix is reserved for system stored procedures, and MSSQL will
try to find it in the master database before looking in the current
database. Even if it doesn't resolve your issue, it's best not to use sp_
for your own procedures.

Simon

[Greg D. Moore \(Strider\)]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

As a complete guess, try renaming the procedure so it doesn't start with
"sp_" - that prefix is reserved for system stored procedures, and MSSQL

will try to find it in the master database before looking in the current
database. Even if it doesn't resolve your issue, it's best not to use sp_
for your own procedures.

Actually, that's probably a VERY good guess.

And if you can't rename it, fully qualify it. I.e. when you call it you
might currently be doing:

USE FOO
go
exec sp_multi_selectEmployee
Rather, do

use FOO
go
exec foo.dbo.sp_multi_selectEmployee

BTW, http://support.microsoft.com/default...b;en-us;263889 may
help.

This was the first problem we encountered in fixing a performance issue. As
I recall, simply fully qualifying the name got us about 20% improvement.

(a later change gave us 100% improvement.)

Simon

[JimBob]

"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<BG*******************@twister.nyroc.rr.com>. ..

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

As a complete guess, try renaming the procedure so it doesn't start with
"sp_" - that prefix is reserved for system stored procedures, and MSSQL

will

try to find it in the master database before looking in the current
database. Even if it doesn't resolve your issue, it's best not to use sp_
for your own procedures.

Actually, that's probably a VERY good guess.

And if you can't rename it, fully qualify it. I.e. when you call it you
might currently be doing:

USE FOO
go
exec sp_multi_selectEmployee
Rather, do

use FOO
go
exec foo.dbo.sp_multi_selectEmployee

BTW, http://support.microsoft.com/default...b;en-us;263889 may
help.

This was the first problem we encountered in fixing a performance issue. As
I recall, simply fully qualifying the name got us about 20% improvement.

(a later change gave us 100% improvement.)

Simon

Thanks for your help everyone. I tried the suggestions above and they
had no effect. I am moving away from using the sp_ naming convention
anyway for future development after reading this thread and some other
resources on the net.

I did figure out the locking problem and it was caused by my asp page.
The command object was using adOpenStatic and adLockOptimistic. I
have no idea why I used those, so I changed it to adopenforwardonly
and adlockreadonly and it no longer recomiles.

[Erland Sommarskog]

JimBob (ma*********@hotmail.com) writes:

I did figure out the locking problem and it was caused by my asp page.
The command object was using adOpenStatic and adLockOptimistic. I
have no idea why I used those, so I changed it to adopenforwardonly
and adlockreadonly and it no longer recomiles.

I can't really say why your settings caused recompiles, but it may have
been on temporary objects that ADO creates.

Anyway, it sounds like you are using server-side cursor. Set .CursorLocation
to adUseClient instead.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp