473,405 Members | 2,262 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Stored Proc Recompiling over and over

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.
Jul 20 '05 #1
4 2272

"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
Jul 20 '05 #2

"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

Jul 20 '05 #3
"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.
Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server...
1
by: VM | last post by:
I'm working on a win appication that is constantly querying a small-sized DB. Until now, I've been using Selects from within my app but, all of a sudden I remembered of an application I was...
5
by: Jason Collins | last post by:
There are a number of stored procedures involved in sql server based session maintenance: - TempUpdateStateItemShort - TempResetTimeout - TempUpdateStateItemLong etc. These stored procs are...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
1
by: E.T. Grey | last post by:
I have been busting my nut over this for pretty much most of the day and it is driving me nuts. I posted this to an mySQL ng yesterday and I have not had any response (I'm pulling my hair out...
3
by: mandible | last post by:
I'm trying to call one stored procedure inside another. I was wondering if this is possible Some ideas I was toying with is putting the first stored procedure inside of a temp table but haven't...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
4
by: PJackson | last post by:
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.