June 10, 2022 at 8:03 am
I'd like to hear some others' opinions on this.
If you are building an SSRS report which requires a non-trivial amount of T-SQL, do you tend to embed the SQL in the report itself, or do you put the T-SQL in a stored procedure and call that from your SSRS report?
I'm having this discussion with a colleague who has the opposite point of view to mine, though I won't say which way I prefer just yet.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 10, 2022 at 8:29 am
I have a (bad) habit of putting the SQL into the reports when building, but I do think that Procs are actually better; I just end up not always migrating them over cause I was lazy. A few of reasons for my thinking procs are better are:
ALTER it, and deploy, than load up the SSRS project, find the report, open the report data, past that into SSMS (or similar), fix, paste back, and deploy.AddWithValue issue; it treats all string data types as an nvarchar. As a results a clause like WHERE VarcharColumn = @StringParameter can be terrible for performance.ALTER the proc and refresh the report, no need to come out of the preview, etc, and reload the report, and hope SSRS doesn't choose to use the cached data (I "love" it when it does that...).Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 10, 2022 at 1:42 pm
I try to always use procs. I don't know of a good reason not to. It's visible, editable , & reviewable in superior query-editing tools such as SSMS. And it's available to other clients besides SSRS in case you change or supplement reporting strategies or use in other applications.
June 10, 2022 at 2:05 pm
Thanks for the replies. Thom, that caching behaviour you describe catches me out approximately once a month!
I'm glad that you both think along the same lines as me. I can imagine, for example, having a hundred reports deployed and then going through a refactoring exercise where one of the well-used DB columns is renamed. Pretty straightforward to handle when all of your code is in procs, but mighty time-consuming if all of the RDLs have to be checked and (maybe) edited. And after that, the release itself requires additional coordination.
I have not tested this to any degree, but the fact that the code in the procs is compiled should also help performance.
Everything feels more controlled when as much logic as possible is in the database itself ... in the hub of the wheel rather than in several of the spokes. Trying to assess the effects of database changes when code is spread across different systems takes longer and introduces a greater risk that something will get missed.
I would like to hear more counter-arguments though, in addition to laziness! One might be that changes to a report require only an SSRS release, rather than DB + SSRS.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 10, 2022 at 3:41 pm
Those are all good points in favor of using stored procedures. Something else to consider though is who is in charge of creating reports? What permissions do they have in SQL? Is a DBA going to have to get involved anytime a report gets changed or migrated? Plus a lot of changes will likely require modifying the report itself too.
June 10, 2022 at 9:41 pm
What's the best way to segregate the SSRS stored procedures from the rest, put them in their own schema, and then grant users execute on the schema? (and SSRS devs create/modify rights?)
... says the guy who is absolutely not a DBA.
June 12, 2022 at 11:17 am
What's the best way to segregate the SSRS stored procedures from the rest, put them in their own schema, and then grant users execute on the schema? (and SSRS devs create/modify rights?)
... says the guy who is absolutely not a DBA.
An answer from one non-DBA to another!
Own schema ... yes, I do that. But I don't do the GRANT EXECUTE bit. Instead, we use a single read-only user for the database connections in SSRS and control access to the various reports from within SSRS, usually at folder level. If a user has access to a report, they can see all of the data that is available to that report, and that works for us.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 12, 2022 at 11:28 am
Those are all good points in favor of using stored procedures. Something else to consider though is who is in charge of creating reports? What permissions do they have in SQL? Is a DBA going to have to get involved anytime a report gets changed or migrated? Plus a lot of changes will likely require modifying the report itself too.
We're a small team in a small company. Only developers modify reports, so the issues you are hinting at are not (usually) issues in our environment. However, if there are unrelated database changes being developed and we want to quickly make changes to a report and promote to production, we may have to get creative with our VCS (git) to keep QA and production database branches in line. Embedding all code in the SSRS report avoids this issue.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 12, 2022 at 4:46 pm
I use stored procedures for ease of amending. But only for more involved queries, and very rarely for the queries that build the SSRS parameter lists of values. The one big downside though is passing a multi-valued parameter to the stored procedure. IIRC - don't use SSRS now - it has to be passed as a single varchar and then 'split' within the stored procedure.
June 13, 2022 at 12:22 pm
The one big downside though is passing a multi-valued parameter to the stored procedure. IIRC - don't use SSRS now - it has to be passed as a single varchar and then 'split' within the stored procedure.
Good point, though I don't see it as a down-side. I usually split out all of the items straight into a temp table with a clustered PK and then join from that temp table to whichever table I am filtering (assuming that's what the parameter is doing).
Took a bit of extra time for the initial implementation in the very first proc, but now pretty much boiler-plate code.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2022 at 12:46 pm
I must admit, the lack of support for table type parameters in SSRS is frustrating. Though, I also feel like the opposite syntax that SSRS allows (SomeColumn IN (@Variable) ) teaches people incorrectly that that syntax works in T-SQL, where as what SSRS actually does is it removes the reference to @Variable and replaces it by injecting the literal values. For long lists, this can actually have some very poor performance. And (as I mentioned earlier), all these values are likely (injected as) nvarchar values, which for many may well cause implicit casting of the column in the WHERE; which we all know is awful for performance. I'd take a delimited list I can control over injection from an application I can't any day.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 17, 2022 at 4:42 pm
My answer is that it depends. Sometimes the report parsing logic is so specialized that it will never be used elsewhere. In those cases I sometimes just slap it in the report. The safe answer is to use procs. If there is a performance issue that would definitely matter. I have never noticed much of a performance difference between the two.
June 18, 2022 at 7:42 pm
You know what I'm going to say, Phil. SSRS is... an application. You already know the rest.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2022 at 10:32 am
But I don't know the rest, Jeff - what do you mean?
June 20, 2022 at 2:40 pm
But I don't know the rest, Jeff - what do you mean?
Understood.
There are other things to consider but let's start off with some of the basics...
Ask yourself what it takes to make changes in the SQL code that's embedded in SSRS... even just a small tweak that changes nothing about the interface with the actual report. Then ask yourself how much of that you would have to do if SSRS were calling a proc instead. Also ask yourself how quickly you could roll back a change if it went awry once it hit prod. Last but not lease, ask yourself how you would deploy such changes to multiple installations of SSRS.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply