April 5, 2011 at 2:41 am
What are the pros and cons of using a stored procedure to produce the data for report versus putting the sql query directly into the dataset?
April 5, 2011 at 10:46 am
I'm new at building reports, and I had the same dilemma.
After trying it both ways, I now go for stored procedures over SQL directly in datasets.
Several reasons I found for this (you'd have to google to verify what I'm saying though):
1) In a stored procedure, the query plan can be cached, meaning that query runs faster. When the SQL is provided to SQL Server from a dataset, it cannot cache the query plan, so it has to calculate it each time. (A query plan is the plan for how SQL Server will get the data).
2) With SQL in datasets, it means that if you want to make a change, you have to open multiple reports, make the change and re-deploy them. With stored procedures, you can make a change by updating the stored procedure - no re-deploying necessary. (Plus all your stored procedures are in one place vs rdl files that could be saved anywhere).
3) I don't know this for sure, but I'm guessing that there's a way to query the meta data of stored procedures (so if you wanted to change a column name, you can see all the stored procedures that use that column name). If so, that would be a big benefit. If not, then at least all the stored procedures are in one place so you can quickly search through them.
4) I build drill-down reports (where one report shows the total by office, then you can click on an office to see more information for that office). With stored procedures, I was able to build one stored procedure for both the drill-down and the summary report. With datasets, I had to have a copy of the same SQL in both the summary and the drill-down reports - making maintenance that much harder.
5) You can control permissions on stored procedures a little easier. Anyone can open an RDL file and edit the SQL without anyone else knowing, whereas you can lock down stored procedures to allow people to view a stored procedure but not modify it.
6) On the flipside, stored procedures are a little more complicated to write (or teach other people to write). It's a step towards development/programming (which can be hard if you're coming from the Access drag-and-drop world of query building).
This is what I can think of off the top of my head - if anything else comes to me I'll let you know.
Leonard
Madison, WI
April 6, 2011 at 3:44 am
Many thanks for your detailed, useful comments. The cacheing of the query plan may well be the key for me.
April 6, 2011 at 4:54 am
Stored procedures are great because they could possibly be used by other software. On the other hand, it's no use unless you actually have the rights granted to create/alter stored procedures in the database (most sites would be hesitant to allow it, especially in production).
I don't utilise drill-downs as much as I should, though when I do I often have to write different queries so that one summarises and one doesn't (allowing RS to do the grouping itself can take forever, let alone passing all that unsummarised data over the network). I'd like to know how you're doing it.
As for cached execution plans... it probably depends on how often reports are used. If you're running a few a second it probably adds up. If it's a few a minute, it probably doesn't.
April 6, 2011 at 5:32 am
The only downside I see with using stored procedures is when you want to pass a multi-value filter as a parameter. In that case you need to parse out the individual filter values.
April 6, 2011 at 6:45 am
Martin,
I just blogged about this very issue at: http://www.sqlchick.com/entries/2011/4/4/pros-and-cons-stored-proceduresvs-embedded-queriesvs-views-i.html
Hope it helps.
Melissa
April 6, 2011 at 6:50 am
One benefit to using stored procedures is that as long as your interface remains the same (that is, the parameters to the stored procedure), you can change the stored procedure without having to redeploy your code to your users. If you change your report file or the code around it, you have to redeploy that change to all users. Depending on the type of application that might be easy (say for a web app) or more difficult (say, for a desktop app). It's much easier to change a single stored procedure than it is to deploy an update to several users.
April 6, 2011 at 7:16 am
Melissa,
Thanks for the link to your blog, that was useful and timely post! The link from there at the end provided good technical reasons for using stored procedures.
Martin
September 13, 2018 at 3:58 am
Scott Murray-240410 - Wednesday, April 6, 2011 5:32 AMThe only downside I see with using stored procedures is when you want to pass a multi-value filter as a parameter. In that case you need to parse out the individual filter values.
DECLARE @College AS XML
SET @College = cast(('<a>'+replace(@Colleges,',' ,'</a><a>')
+'</a>') AS XML)
Gordon Barclay
September 13, 2018 at 7:40 am
Gordon Barclay - Thursday, September 13, 2018 3:58 AMScott Murray-240410 - Wednesday, April 6, 2011 5:32 AMThe only downside I see with using stored procedures is when you want to pass a multi-value filter as a parameter. In that case you need to parse out the individual filter values.Not true, you can use XML in the stored procedure. In a stored procedure I pass a multi value parameter and then use the following code where the variable @Colleges contains the multiple values.DECLARE @College AS XML
SET @College = cast(('<a>'+replace(@Colleges,',' ,'</a><a>')
+'</a>') AS XML)This is then used in an IN statement e.g.
SELECT *
FROM tablename
WHERE feildname IN (SELECT A.value('.', 'NUMERIC (16, 0)') FROM @College.nodes('a') AS FN(A))
Actually, it is true. You do need to parse the out the individual values. XML is just another way to do this. It's also not a terribly efficient way to do this. You are much better off using a string splitter.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2018 at 4:04 am
drew.allen - Thursday, September 13, 2018 7:40 AMGordon Barclay - Thursday, September 13, 2018 3:58 AMScott Murray-240410 - Wednesday, April 6, 2011 5:32 AMThe only downside I see with using stored procedures is when you want to pass a multi-value filter as a parameter. In that case you need to parse out the individual filter values.Not true, you can use XML in the stored procedure. In a stored procedure I pass a multi value parameter and then use the following code where the variable @Colleges contains the multiple values.DECLARE @College AS XML
SET @College = cast(('<a>'+replace(@Colleges,',' ,'</a><a>')
+'</a>') AS XML)This is then used in an IN statement e.g.
SELECT *
FROM tablename
WHERE feildname IN (SELECT A.value('.', 'NUMERIC (16, 0)') FROM @College.nodes('a') AS FN(A))Actually, it is true. You do need to parse the out the individual values. XML is just another way to do this. It's also not a terribly efficient way to do this. You are much better off using a string splitter.
Drew
Yes I will agree that It is indeed parsing, however the STRING_SPLIT function is only available with compatibility level of 130 or greater, this maybe an issue and is not a factor in using XML. I also take issue with the efficiency point, I have found this to be at worst a negligible factor.
Gordon Barclay
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply