Menu
I currently have two stored procedures that are meant to do the same thing. I was given two ideas on how to retrieve a fairly complex set of data, so I wrote them both and now I need to determine which one is more efficient.
I have tried using Sql Profiler, client statistics, and reviewing Execution Plans, but the results appear inconclusive. Which of the two stored procs that executes more quickly changes from execution to execution.
I am guessing the buffer cache, statistics, and network traffic are causing the fluctuation in results. I can run the same stored proc 10 times in a row and get very different Durations (somewhere between 900ms and 1400ms. I know that's not THAT big, but it makes my decision more difficult).
I know I can clear the buffer cache and reset the statistics, but I am working with a dev server that is used pretty frequently by others. I don't want to slow them down with my shenanigans.
The amount of data I am working with is certainly smaller than it will be in production, so it would probably help to dummy up a realistic amount of data.
What I am wondering is whether or not there is a nice way to run these two queries and determine which performs better. Maybe some software that will run each query 100 times and generate a nice chart or something? I don't want to totally remove statistics and cache from the equation because those will be in play in the production.
If it turns out that the queries are fairly interchangeable, Ill ask a follow up question about which one is theoretically more efficient.
Thanks!
UPDATE: Okay, looks like I need to add more data and then get back to you. Maybe that is all I need to do. Also going to take the time to read Dalorzo's post. I will let you know if the results stabilize with a more realistic test.
UPDATE Oh yeah, it became much more conclusive when I added more data. One of the stored procs barely changed when I added more data, the other one took about 3x as long. Thanks everyone! I figured more data would help but was hoping there was a way that didn't involve cramming a bunch of stuff in to the DB. Much appreciated.
413
Overall, stored procedures outperform dynamic SQL. They are faster, easier to maintain, and require less network traffic. The rule of thumb would suggest using stored procedures in scenarios where you don't have to modify queries, and those queries are not very complex.
Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at runtime.
The room was pretty evenly split on the answer: some thought the stored procedures will always perform faster while others thought it wouldn't really matter. In short, the answer is that the query optimizer will treat a query defined in a stored procedure exactly the same as a query submitted on its own.
With more test data the timing might stabilize because caching plays less of a role. Also the difference between the procs will become more pronounced. You will also be able to observe their asymptotic scaling behaviors. Different kinds of joins and indexing strategies scale differently with added data.
Increase the amount of testing data to the maximum you expect to see in production in a few years from now.
In addition, I'd compare not the average but the 95% worst execution time. You probably want an algorithm that behaves in a stable way. Erratic performance variations can lead to problems.
You could go one step further and generate a histogram of execution times for each function by executing them 1000 times each. That allows you to see the whole distribution and pick the one you like more. That's easy to chart with Excel as an XY Scatter Plot.
178
What you need is to have the execution plan of both queries to be able to determine which one is better.
This is a good article on this topic:
http://sqlmag.com/blog/comparing-execution-plans
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
