July 11, 2011 at 1:55 pm
I was asked this question recently in an interview and I didn't know how to answer the question with confidence because I've never put much thought into it before.
What works better? 1. Running a stored procedure, or 2. a query for the data sets in a report for SSRS? I'm assuming all of the processing is done on the server when running a SSRS report. Is that not correct?
July 11, 2011 at 2:33 pm
During dev, I use query (allows to use wizards and for fast edits of adding / removing stuff). However this might not be so true if you have templates to use. Also if you need to do a test version for a user then you can give him the version with query while you work on the version with SP. This is nice if you only have 1 server to play with (no dev or QA environement). That way you don't need to have n versions of the reports live at the same time (including permissions)
After dev switch to SP. Allows for reusability and updates to datasource without having to re-deploy.
The 1 caveat is that if you have something like this :
where MyCol IN (@Multi_Select_Parameter) then you'll have to switch to a splitter function in the proc because that isn't handled the same way with a straight query vs sp (the straight query uses sp_executesql).
I think this question was about showing your true experience with the product. Someone who's just read a book and then did 1-2 reports wouldn't have gone thru this whole thought process (or even a couple months part time experience might not be enough). IIRC this happened over weeks for me and 12's of reports... and I'm still playing with more options to find the real "best path" for me and our company's needs.
July 11, 2011 at 3:01 pm
Well I've been using both SP and queries. Probably 70/30 queries to SP. The more complex it is, the more likely I am to use a SP simply because it is easier to edit. And like you said, you don't have to redeploy. From a processing end of the question though, there really isn't a difference, correct?
July 11, 2011 at 4:23 pm
adams.squared (7/11/2011)
Well I've been using both SP and queries. Probably 70/30 queries to SP. The more complex it is, the more likely I am to use a SP simply because it is easier to edit. And like you said, you don't have to redeploy. From a processing end of the question though, there really isn't a difference, correct?
Well the one tiny weeny thing is that the plan will be flagged differently (adhoc vs prepared) in the cache but ya, they are 99.999% the same at that point.
July 16, 2011 at 11:00 am
My (admittedly untested) guess is that executing an ad hoc query in RS for a dataset is no different from running one in SSMS or any other application: you lose the benefits of SP plan cache and performance (see this article on plan cache and the 2008 option for optimizing for ad hoc queries: http://sqlskills.com/BLOGS/KIMBERLY/post/Statement-execution-and-why-you-should-use-stored-procedures.aspx).
For my money, having nearly all RS queries saved to SP's is a huge benefit for security, reusability, and ease of maintenance. So much easier to modify an SP than open VS and edit a DataSet definition. Development is easier too: you're going to need to use SSMS to develop your source query anyway, so you can trouble-shoot it and test performance/execution times, right? So why add another step of copying/pasting into RS? Need to change the query? Oops -- open SSMS, edit, re-test performance/results, open VS, copy/paste....
Does that help?
Rich
July 16, 2011 at 1:03 pm
rmechaber (7/16/2011)
My (admittedly untested) guess is that executing an ad hoc query in RS for a dataset is no different from running one in SSMS or any other application: you lose the benefits of SP plan cache and performance (see this article on plan cache and the 2008 option for optimizing for ad hoc queries: http://sqlskills.com/BLOGS/KIMBERLY/post/Statement-execution-and-why-you-should-use-stored-procedures.aspx).For my money, having nearly all RS queries saved to SP's is a huge benefit for security, reusability, and ease of maintenance. So much easier to modify an SP than open VS and edit a DataSet definition. Development is easier too: you're going to need to use SSMS to develop your source query anyway, so you can trouble-shoot it and test performance/execution times, right? So why add another step of copying/pasting into RS? Need to change the query? Oops -- open SSMS, edit, re-test performance/results, open VS, copy/paste....
Does that help?
Rich
Actually SSRS use sp_executesql in the background so you get the same benefits as the sp on that level.
Moreoever there's little benefit in a OLAP environement. In our little shop we run only a few 1000 reports per week. But the same report can be run for 1 day of data up to 4-5 years. So plan reuse here is actually dangerous. I automatically set my query to recompile as soon as I have a date parameter. The 1 ms it takes to recompile is well worth the saving of avoiding a index seek + bookmark lookup on a 18 M rows table.
Completely agree on security and reusability.
However I set my security through windows groups in AD and assign permissions there to access the reports so it's not really a big plus in our shop.
July 16, 2011 at 8:12 pm
Ninja's_RGR'us (7/16/2011)
Actually SSRS use sp_executesql in the background so you get the same benefits as the sp on that level.
Ah, thanks for that point. Makes sense that would be the case.
Moreoever there's little benefit in a OLAP environement. In our little shop we run only a few 1000 reports per week. But the same report can be run for 1 day of data up to 4-5 years. So plan reuse here is actually dangerous. I automatically set my query to recompile as soon as I have a date parameter. The 1 ms it takes to recompile is well worth the saving of avoiding a index seek + bookmark lookup on a 18 M rows table.
Very good point that I didn't consider in my reply. Most of our reports run with single-valued parameters from a relatively circumscribed set of options, so the parameter sniffing/optimization problem isn't in my mind. That could be a huge issue, as you pointed out. Chalk up another "it depends" answer!
Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply