March 24, 2009 at 12:14 pm
All Experts,
I have a basic question in SSRS.
Should we use the sql commands in the datasets or have stored procedures?
Which has more advantages?
Or
Is there any other way where we will have better performance and control.
Thanks for your time.
:hehe:
March 24, 2009 at 12:53 pm
Hey Sandy.
I'm not the "expert" that you seek, but I can offer a couple of thoughts.
I prefer to write as much of my stuff in Stored Procedures which I then execute from my compiled code.
1. Using stored procedures is much more secure as it prevents any possibility of injection attacks.
2. SQL Server will compile stored procedure and based on my limited experience, seems to yield higher level of performance.
3. I can "tweak" stored procedures much easier than recompiling my program code and redeploying it to the servers it needs to run on.
In my compiled application code, I have the choice of firing off my stored procedure by using a .NET table adapter, or from instantiating a SQL Command object. It's quicker (development-wise) to invoke it from a table adapter, but the table adapters have a 30 second timeout window so if your stored procedure takes longer to execute, it will throw timeout errors back to your code. If you instatiate a SQL Command object, then you have handy access to the Command Timeout value to allow it to run as long as you want. I frequently need this because I deal with sizeable blocks of data and most operations against it seem to take longer than 30 seconds.
Hope this might be of some benefit to you.
March 24, 2009 at 1:07 pm
personally the sql commands I have for reports don't travers more than 5 or 6 tables. If you are traversing a lot of tables, then pre-compiled is better. But most reports I use don't, so just use sql statements in ssrs 2008. 2005 is ok, but I don't like it. 2008 is much better with ssrs. I would recommend 2008 because of it's use with datasets, very good stuff.
March 24, 2009 at 1:10 pm
I would also prefer the usage of stored procedures. If you write the SQL to the report you may not be able to assert if all reports still work.
Greets
Flo
March 24, 2009 at 1:52 pm
I prefer to keep such in the database, in procs. That way, if a table needs to change, it's much easier to find all procs that depend on the table, than it is to find all reports that depend on the table.
A select from sys.sql_modules will find the code with the table, pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 24, 2009 at 2:06 pm
Another benefit I see in using procs is the ability to make changes later on. If a business rule changes that requires a change to your report, more than likely you'll be able to make the change without having to redeploy the report. Just change the procedure and the net time it's called it returns the new data.
-Luke.
March 24, 2009 at 2:13 pm
Larry Kruse (3/24/2009)
1. Using stored procedures is much more secure as it prevents any possibility of injection attacks.
[font="Verdana"]Not entirely true, sad to say. Certainly it makes it harder, but it doesn't prevent any possibility, particularly if dynamic SQL is used within the stored procedure.
[/font]
Larry Kruse (3/24/2009)
2. SQL Server will compile stored procedure and based on my limited experience, seems to yield higher level of performance.
[font="Verdana"]Again, not always. There are some issues with parameter sniffing, where the stored procedure gets compiled with some assumptions about the parameters that are wrong and hence uses poor choices for the query plan.
[/font]
Larry Kruse (3/24/2009)
3. I can "tweak" stored procedures much easier than recompiling my program code and redeploying it to the servers it needs to run on.
[font="Verdana"]That to me is the primary reason to use stored procedures. Well, it also makes it easier to manage things like auditing table changes, history tracking and so on if all modifications are made through stored procedures. But the truth is, even the downsides of stored procedures can be tuned for, and it's easier to have a SQL expert focus on the stored procedures and an application expert focus on the application, than to couple the two together. Then they can be bug-fixed/tuned independently.
From an architecture point-of-view, using stored procedures for all database access lowers the coupling between the application and the database. The database effectively presents an API to the application.
So in general, I would say your third point is a good example of why decoupling the application from the database is such a good idea.
There are always exceptions.
For example, if the application is using something like SQL Server Mobile (or whatever it's called these days) and something like LINQ over the top of that, I have no issues whatsoever with that approach. Obviously I would still like the communication between the application and a central store to be via an API, but that API could be through something like BizTalk or SQL Server Broker or X-Query.
Another exception would be with reporting databases or data warehouses which are designed to support ad-hoc querying, and the SQL is generated by toolsets.
[/font]
March 24, 2009 at 3:58 pm
I guess it all depends on your environment and the maturity of your database.
in sql2008 I have found it best to first find the final output you want to achieve, and then write the sql for the variables being used. If your database is changing a lot, it might be nice to have some reporting tables for an interim period.
March 24, 2009 at 5:58 pm
Use stored procedures, they are always better than ad-hoc SQL in a variety of different ways. Simply stated, SQL can compile and execute them more efficiently, and thet can be reused by subsequent processes.
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply