February 13, 2017 at 7:19 am
Hi all
Question for the group... which is better or what are the pros and cons of views vs stored procedures?
If you have a reporting tool such as crystal reports and you want end users to create reports, one method is to create views of tables joined to pull together related tables for them to use in crystal reports for reporting purposes. The creation of these views, does it create a load on the database that would not be needed if the use of stored procedures to call the same data was created in a more tailored manner?
I know the tailored stored procedures would require time to create for each report, but is the pay off in the long run by not having views that have to join several tables to pull back a small set of data.
Curious of this groups thoughts on the pros and cons or if one can say one is better than the other process.
Thanks
Dean-O
February 13, 2017 at 8:05 am
In your case, I don't imagine there'd be much difference. If you need to join tables, you need to join tables, whether you do that in a view or in a stored procedure. What you need to be careful of is having views with lots of joins which you then join to each other. Keep it as simple as you can. One stored procedure per report might be the way to go, depending on the number and the complexity of the reports. There's no reason you can't use a view in a stored procedure definition if you need to.
John
February 13, 2017 at 9:29 am
rochesterd - Monday, February 13, 2017 7:19 AM...The creation of these views, does it create a load on the database that would not be needed if the use of stored procedures to call the same data was created in a more tailored manner?I know the tailored stored procedures would require time to create for each report, but is the pay off in the long run by not having views that have to join several tables to pull back a small set of data...
Since you're allowing users to create their own reports, views may work better, since the reports they create could have very different filters creating different WHERE clauses on the data. To handle that in stored procedures would either require that they always followed a specific methodology for filtering rows and have a separate stored proc for each one of those methods, or a catch-all type of query that are notorious for bad performance.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
With views, the execution plan would be generated for the query in each report, so there would be more execution plans in the plan cache but they will be tailored to how each report works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply