September 12, 2008 at 2:53 pm
I have been reading up on stored procedures and I have some questions:
i) I have begun to grasp that stored procedures execute faster than in-line SQL statements because an execution plan is already created and they are pre-compiled.
However it says you can also pass variables into a stored procedure, for example you can pass a variable to only search particular records in a table (where a field value is equal to the passed parameter). So how an earth can an execution plan already be created when SQL Server does not yet know which records it will be searching for? So are stored procedures that pass parameters still faster than in-line SQL?
ii) Expanding on the issue of parameters in a stored procedure, is it possible to pass a table name into a stored procedure as a parameter? I.e. say you have a table of cities in California, and a table of cities in Nevada, can you create one stored procedure to select cities from either tables? Or do you need to create two seperate stored procedures for each state?
iii) In SQL Server 2000 Enterprise Manager, there is a 'create stored procedures wizard', but it only allows you to create 'update', 'insert', and 'delete' stored procedures. Why does it not allow you to create 'select' stored procedures? Is this because simple 'select' queries are not complex enough to merit the use of stored procedures for their execution?
September 12, 2008 at 3:01 pm
1 - yes, they are still faster than in line SQL. They are precompiled and SQL makes some assumptions on what it thinks will likely be passed to it.
2- that can be done - you could do it with or without dynamic SQL
3- you can create a select, and yes, it will be faster than inline code. it just lacks the template to do so in SSMS, but you can certainly do it in a query window
September 12, 2008 at 3:29 pm
Actually - let's clarify some things:
- both stored procedures and ad-hoc queries go through the SAME process when they're called for execution. They both follow the PARSE / RESOLVE / OPTIMIZE / COMPILE / EXECUTE process on the first execution. They both end up execution plans, which are then saved and might be reused if they are called several times.
- Exec plans, while saved, aren't always reused. The saved execution plans hold some amount of information as to the assumptions that was used in terms of row counts, etc... As part of the decision process as to whether to use/reuse an existing query exec plan, the old assumptions are checked (assuming you don't have "parameter sniffing" going on), and if the cardinality is different enough, the old plan is discarded and a new one created. Parameter sniffing can happen when the same assumptions are used over and over and over again, even if they don't apply, resulting in less than optimal exec plans.
- Like Adam was pointing out - the wizard in 2000 had some limitations. You can certainly create your own from scratch. It seems the dev team just wasn't inspired to come up with a "select" template.
In my opinion - the perf "advantages" you see with using stored procedures are more human factors than anything else:
- the DB engine is VERY picky about whether two queries are the "same" or not. Differences in what humans might consider minor (like - case or prefixing objects with their owners or not) might disqualify ad-hoc queries from re-using plans. In this sense - SP's can help improve your odds of reusing exec plans.
- there's often a mindset difference when building something "permanent" (i.e. stored procedures) vs building something ad-hoc. In shops where perf is important, stored procs get more attention (often by people dedicated to writing/dealing with DB code) than ad-hoc query code (often built by devs, who may or may not be "experts" at optimizing/writing good SQL code). You also tend to see error-handling, comments, etc... in stored procs where you would rarely if ever see those in ad-hoc code. In other words - you tend to get "better" code in SP's than you might in ad-hoc.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2008 at 3:46 pm
Thanks for the reply, that has reassured my confidence in using stored procedures.
On your response to number 2: when passing the table name into the procedure usng dynamic SQL, do you mean using the EXEC("string") command? If so will you get any of the performance advantages of stored procedures using 'EXEC'? Surely it cannot prepare an execution plan before hand if it does not know what is in the EXEC command string??
How would you pass in the table name without using dynamic SQL?
September 15, 2008 at 7:04 am
if you use sp_execute, the plan will be more likely to be reused on multiple executions. Otherwise, if you don't want to use synamic SQL, you could have conditional statements to see what table you are passing in and execute a black of code with the correct table name, but this approach means you would need to know in advance what tables they will pass and account for all of them.
September 15, 2008 at 7:57 am
The execution plan isn't prepared ahead time. When you submit EXEC the second time, it checks to see if it has a plan for that exec string. If it does, it's reused.
What do you mean pass in the table name?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply