Stored Procedure Efficiencies
-
I've read about and understand most of the benefits from stored procedures - code re-use, query path, ... All the benefits are written in a high level, grandiose fashion while I'm down here at ground level. I'm just curious about a simple little select statement and if the stored procedure will help my execution time. Per example:
My application shall be performing the same sql statement nearly 400 times during one processing cycle except with one different parameter (there is a reason why I'm not retrieving all the info at once, but that is beyond the scope of the question). As an example, the sql would be:
SELECT name, address, country FROM Person INNER JOIN Address on Person.addressID = Address.addressID WHERE personID=1;
I will then do some processing with the results (using C# language) and then run the same query, but with personID=2 - repeat several hundred times.
If I create a stored procedure and pass in the personID everytime, am I gaining any processing speed or processing efficiencies?
-
Hi,
For the query you give as an example, on the face of it you would probably gain only a modest gain in performance. However, since you are executing this same procedure hundreds of times per minute, then don't underestimate the performance benefits of an SP ...
Some basics:
When you run a standalone SQL query (e.g. in Query Analyzer) for the first time, the first thing that happens is that the query optimizer has to calculate the most efficient way of joining the tables together to retrieve the data. This takes a brief, but measurable amount of time. To save effort, the resulting "query plan" is stored in a system table, and if the exact same query (with the same parameters and everything) is ever run again, the stored query plan is used, so there is no need to re-calculate it.
When running a stored proc for the first time, a similar thing happens - the query is optimized and a "query plan" is generated based on the runtime parameter passed fir that execution. This plan, too, is saved in a system catalog. When the SP is executed again, that plan is re-used even if the runtime parameter happens to be different. So, SPs do not waste time re-generating query plans each time they are run. This can be an advantage, although it can also be a disadvantage (some runtime parameters would benefit from a different plan to others, but generally the saving of the plan is a good thing). If you're executing the SP a large number of times, the lack of compilation time required can amount to a considerable performance saving.
There are other benefits to stored procedures:
- All the effort is done at the server (there virtually no network traffic required simply to execute the query)
- Security is simplified. There is no need to give users access to tables directly. If they have execute permissions on the SP, they don't need permissions on any tables - they get those implicitly via the stored procedure code. This means that you control ALL the actions that users can perform on the database. They can only perform actions that are defined within the stored procedure code.
-
i believe if you use a sp it's compiled on the sql server, where if you just passed on the query with a parameter it would need to be compiled every single time
-
It seemed like several sites talked about gaining stored proc efficiences through the use of a persistent connection to the database. Is this true?
I've also been looking into the "use the sp_executesql stored procedure instead of the EXECUTE statement".
What does the sp_executesql buy me?
And thanks for the posts - big help!
-
Back to the basic problem here. Why are you calling the same code 400 times in a row to select similar data? Why can't you query the whole set in one batch >>> that'll give you the biggest performance improvement of all (not that sp performance is negligable).
-
I'm not exactly calling it 400 times in a row, but I may call it 400 times during a processing routine. We are processing tax returns (a very specialized type). We may be doing 2000 returns and depending on previously calculated values, 400 of the returns need the values based on the stored procedure (or other stored procedures). Part of the business rules specify we must calculate one individual at a time (based on how end users (accountants) use the applcation). The logic must also be quite procedural as all of the QA individuals are CPAs with a general knowledge of programming languages (the code mimics the steps followed in a tax form - Add line 7 plus line 8 and if greater than 0, fill out schedule payMoreMoney).
-
I see said the blin man.. I guess there are always exception even for set based processing on sql server
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply