Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Stored Procedure Execution Expand / Collapse
Author
Message
Posted Wednesday, June 9, 2010 10:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.


That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.
Post #934776
Posted Wednesday, June 9, 2010 11:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:11 AM
Points: 889, Visits: 2,459
UMG Developer (6/9/2010)
Nadrek (6/9/2010)
B) Always use three part naming, so you either execute:
Adventureworks.Person.(otherprefix)ReturnSomething
or
Master.dbo.(otherprefix)ReturnSomething

both of which make it explicitly obvious what you're calling.


That sounds nice, but it causes problems if for example you need to duplicate the DB to do testing, and don't have a different instance to put it in. Suddenly your SPs are running in the wrong DB, and you would have to go through and refactor all of the code. However, I do agree with using two part naming.


True, "best practices" need to be evaluated in relation to any given situation and set of constraints one is under. As the situation and the constraints change, so too do the "best practices" that best fit.

Since I've often dealt with systems that do a reasonable percentage of cross-database, same-instance calls, three part naming across the board makes sense; if you move to differently named database sets on the same instance, you're going to have to do a lot of careful global search and replace anyway.
Post #934782
Posted Wednesday, June 9, 2010 3:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:18 PM
Points: 86, Visits: 217
Yes, you should definitely avoid using the sp_ prefix. Not just the performance hit, since it always checks the master and resource databases first. Also, the next version of SQL Server could have a new system stored procedure with the same name; you'ld be scratching your head for a long time to figure out why you application was suddenly failing.

However, as a DBA, you can be comfortable using the system stored procedure, even in a develpment database. A programmer can't trip you up by leaving an sp_ stored procedures waiting for you to execute it with SA rights.


David Lathrop
DBA
WA Dept of Health
Post #935013
Posted Wednesday, June 9, 2010 3:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:18 PM
Points: 86, Visits: 217
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache. Queries with unqualified names, including those in stored procedures, have to be fully parsed and processed each time they execute because the name resolution is based on that session's user's default schema.

So if you can use fully qualified names in your environment, it gives much better performance.

If you need a test database, you might consider using another SQL Server instance, a virtual machine, or testing on "developer" workstations.


David Lathrop
DBA
WA Dept of Health
Post #935021
Posted Wednesday, June 9, 2010 3:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.


I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?
Post #935022
Posted Wednesday, June 9, 2010 3:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
UMG Developer (6/9/2010)
DLathrop (6/9/2010)
Full three part naming helps the query optimizer recognize and reuse queries in the execution plan cache.


I understand that you need to use two part naming, but I have never heard that you have to use three part naming for the optimizer to recognize and reuse execution plans. Do you have a link to any documentation that supports this?


That's news to me to.
Post #935023
Posted Wednesday, June 9, 2010 5:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:18 PM
Points: 86, Visits: 217
Oops, I checked. The two part schema.object form of name is considered "fully qualified" for plan caching; you don't need to prefix names with the database.

Sorry for sharing my confusion. [In some contexts "fully qualified name" means three or four part names.]


David Lathrop
DBA
WA Dept of Health
Post #935070
Posted Tuesday, June 15, 2010 1:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 17, 2011 11:42 PM
Points: 41, Visits: 3
I have used the same code but i have return as 5 as result

But i did not execute whole query in batch i have execute it individual

would you please reply me for it
Post #937289
Posted Wednesday, June 16, 2010 7:47 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 5:50 PM
Points: 257, Visits: 601
Good Question. I got it wrong. I did not see that the sp started with sp_.
In my company we follow the standard of not using sp as starting letter for stored procs name and somehow missed to spot this one.
Post #938615
Posted Wednesday, July 28, 2010 5:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
The explanation is wrong.
Explanation: "SQL Server uses the sp_ prefix to designate system stored procedures... A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead."
http://msdn.microsoft.com/en-us/library/ms190669.aspx


Just because you create a procedure in master that is named sp_ does not make it a system stored procedure. I am still very angry at MS for taking away the ability to create system functions in 2k5.

In the example the only reason the sproc in master is executed is that the local procedure was in a non-default schema.
This is why people without Adventure Works were getting different results.


Post #959954
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse