Programming for the web taught me about statelessness, bandwith conservation, and the biggie, SQL Injection. Being the lead programmer I developed standards for the use of stored procedures in everything we did. I learned a lot, made my fair share of mistakes, and developed a deep admiration for stored procedures.
The focus became take a request from a webpage, go to the server, do as much on the server as possible while you are there, and return the results to the webpage. Works great.
So, now I'm back into Delphi and rewriting a desktop application that hasn't been updated since 2004. The jump from D5E to D2010 in itself, has been a challenge. I'm also going from a non-database application to a databse application. The database I have chosen to use is ElevateDB.
One of the reasons I chose ElevateDB was it offers stored procedures. I love stored procedures, they are like black boxes. As long as the interface doesn't change they are good-to-go and offer awesome power.
So, I'm taking all this thinking I developed throughout those web years and applying it straight to Delphi and the application rewrite I'm doing. I know that a stand alone desktop application doesn't have to worry about statelessness, or bandwith conservation, and for the most part SQL injection. But it's hard to re-learn to think... until the rubber meets the road.
The rubber met the road when I decided to build my first report using ReportBuilder. I've never truely had a reporting tool before... even in my web days I built everything from scratch. So, I took the amortization schedule I built as an ElevateDB stored procedure and made that my first report. Everything is going well, the report looks professional, I'm having fun.
Then I start thinking... wow it wouldn't be nice to add a a percentage of payment next to the principal column. Wouldn't it be nice to show the percentage of interest also. And it would be totally awesome to bunch them by the year the payment is due. So, I decide to add two calculated fields to my report, no big deal. PrinPct is simply Payment divided by Principal.
Then I realized...
- You can't add a calculated value from a stored procedure column
- You can't do a group by based on the the year of the payment
- Wow, building a report based on the result set of a stored procedure is not the best approach
"My first thought was, great ReportBuilder doesn't let me do what I want to do. This sucks."Then I realized...
"It's not ReportBuilder, it's me. My thinking is wrong. Stored procedures are not meant to be reported on... they are meant to do the heavy lifting that makes the data easy to report against"I'm glad that this happened to me early on in the process. I would have been quite upset had I created a total database with 30-40 stored procedures all built on the assumption that they could be used as datasets for reporting.
Back to the drawing board... another lesson learned.
Semper Fi, Gunny Mike
I use stored procedures for reports IF the report requires a side effect, like logging, updating other tables, etc. Can't speak for ReportBuilder, but I know SQL Server Report Services let's you add calculated fields to stored procedure results. In Delphi, I use Fast Reports. I think it let's you create calculated fields in addition to the fields returned. I know you can create formulas inside content controls.
ReplyDeletewe also use stored procedures as a dataset for creating reports on my previous company.
ReplyDeleteWhy not load the data from the stored procedure into an in-memory dataset such as a TClientDataSet and then base your ReportBuilder report on the clientdataset? You can then add calculated fields or even reorder the clientdataset to your liking without further affect on your stored procedure.
ReplyDeleteIMHO, Using StoredProcedure is nightmare. Using SP makes my application database dependent. I Don't like it. For reporting, i prefer fastreport. We can extend it's built in function as we want using pascal script;
ReplyDeleteAbout reports, I do not link component-driven reports. I like code-driven report, in which you create your report just as you create some HTML tables, directly from any data (coming from DB, memory, objects, whatever)... You are not stuck to components links to fixed datasets layouts. Less mouse clicks, more keyboard taping... but IMHO worth it. See http://blog.synopse.info/post/2010/06/30/Making-report-from-code
ReplyDeleteWhen you reach SOA world, or high-level Domain-Driven architecture, stored procedures does not make sense. Only exception is batch process of huge amount of data.
Using an ORM would ensure that you work with objects, and rely on DB only for persistence. And you won't be stuck into one DB: what if your client expects an embedded DB, or has already an Oracle or MS SQL license?
Even for a pure Web application, DB side process is a bottleneck.
But see for instance how Microsoft is active in this area: see for instance AppFabric purpose is to host services outside the DB area, providing cache clusters of objects. Such caches would make applications faster and scalable.
In a cloud world, stored procedures are mapped to tables, whereas scalability comes not from DB power, but having objects spread out.
This is a trolling subject, but IMHO stored procedures are mixing database layer and business logic too much. It is tempting to break the n-Tier layer good practice with them.
About reports, I do not link component-driven reports. I like code-driven report, in which you create your report just as you create some HTML tables, directly from any data (coming from DB, memory, objects, whatever)... You are not stuck to components links to fixed datasets layouts. Less mouse clicks, more keyboard taping... but IMHO worth it. See http://blog.synopse.info/post/2010/06/30/Making-report-from-code
ReplyDeleteAbout reporting logic, take a look at CQRS - http://martinfowler.com/bliki/CQRS.html - and dedicated Reporting Databases - http://martinfowler.com/bliki/ReportingDatabase.html
Of course, sounds like specific needs for some "niche" applications. But how knows if you won't need to have an application scale better. Better be prepared, and have an open architecture!
When you reach SOA world, or high-level Domain-Driven architecture, stored procedures does not make sense. Only exception is batch process of huge amount of data.
Using an ORM would ensure that you work with objects, and rely on DB only for persistence. And you won't be stuck into one DB: what if your client expects an embedded DB, or has already an Oracle or MS SQL license?
Even for a pure Web application, DB side process is a bottleneck.
But see for instance how Microsoft is active in this area: see for instance AppFabric purpose is to host services outside the DB area, providing cache clusters of objects. Such caches would make applications faster and scalable.
In a cloud world, stored procedures are mapped to tables, whereas scalability comes not from DB power, but having objects spread out.
This is a trolling subject, but IMHO stored procedures are mixing database layer and business logic too much. It is tempting to break the n-Tier layer good practice with them.
StoredProcs are like any other concept in programming, they are a tool that should be used when it fits. Running reports, changing a single element on a row, row level computations, etc are best left to dynamic SQL and a real programming language. SPs might save a couple milliseconds and are great on non-changing transactions but they often chew up that gain with their many limitations.
ReplyDeleteIn Middle Tier apps stored procs come at high cost as well. Moving processing to the most expensive server with the most shared resources is poor utilization management. I would rather spin up a cheap server to slog through data than offload that work to a pricey database server shared by N other servers doing the same thing.
The results from a stored procedures are no different than the results of a table query. If your reporting tool treats them differently, then the reporting tool may indeed be a significant part of the problem.
ReplyDeleteAs for reporting, that was *ALWAYS* SQL's weakest feature. Indeed, this is mostly why stored procedures were created in the first place - they were the front line for creating a report.
And when it comes to reporting tools, frankly I always found the flat out stink to high heaven when it comes to actual reporting. Sure, they can print out a table verbatim, but gods help you if you want to do something beyond that - if it did not occur directly to the report tool programmer, you are often screwed.
When I want to do something sophisticated, I find that I usually have to create my own reporting tool.