Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Recently, it was suggested that many projects (where I work) should be implemented using some combination of stored procedures (on SQL Sever / T-SQL), SSIS, and SSRS.

In one specific project, SSIS would get a file from a SFTP server. The file contains the daily price of ten stocks. A data warehouse will store the daily price of each stock since inception. As of now, the prices go back twenty-five years. Stored procedures will have the business logic to calculate various performance numbers (i.e. return %) during arbitrary time periods such as the 5 year return at 31 December 2012.

These stored procedures would be used by SSRS to create reports for the end users.

What are the problems/issues using this approach? I feel that vendor (Microsoft) lock is an issue but I don't see that (unfortunately) being enough to change direction. Also, I have a concern about all the business logic being stored procedures. What are the arguments against/for putting this business logic (i.e. calculation of performance numbers) in stored procedures versus using Java (or some other OO programming language) with ORM (such as myBatis)?

share|improve this question

closed as too broad by gnat, GlenH7, MichaelT, Kilian Foth, Dan Pichelman Jul 11 at 21:41

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

6 Answers 6

The biggest advantage of stored procedures is that they are limited to T/SQL statements. That usually keeps complexity down.

A solution in a more general language has to solve many problems that a T/SQL procedure does not: how to map objects to database tables (ORM), how to schedule imports, how to log exceptions, how to connect to the database. That can get very complex, especially if the author tries to solve the problem in a general way.

A side advantage is that T/SQL does not change very often. A stored procedure from 1995 runs fine on SQL Sever 2012. By contrast, a general purpose language from 2008 feels antique and dated today.

share|improve this answer

I wouldn't worry much about vendor lock-in. Changing databases is always a complex challenge; Microsoft isn't getting rid of SQL Server any time soon, so I would feel comfortable building against it specifically.

I've got no experience with SSIS and SSRS, but I do use stored procedures exclusively for database access. I like them because they separate data from consumption--as a programmer, I shouldn't care how I know what the value of a stock portfolio, just what it is. Stored procedures give me the power to do all that work in one place (and in the database, so there is never the urge to try to roll your own set-based logic in procedural code). Performance is also better, especially when you have massive result sets and aggregations.

share|improve this answer

One problem with this solution is that it pre-supposes that the solution be database driven, that the database be a relational database, and that it is acceptable to put the logic in the database. There are several challenges here.

  • Have you tried spikes using non-relational technologies like MongoDB? MongoDB itself is free (though they offer commercial support), insanely easy to get started with, and far less complex than a SQL based approach. Depending on your data structure you might want to consider a column store (like Cassandra), a key value store (like Riak), or a graph database (like Neo4J). If you want to know more about these kinds of databases try checking out the book NoSQL Distilled http://martinfowler.com/books/nosql.html
  • It is sometimes considered advantageous to centralize business logic in as few places as possible. It can make your application far easier to manage when source code is expressive and laid out in a way that is easy for programmers to understand, and it's quite difficult to do this in SQL. Also it's hard to maintain SQL code - there's no decent re-factoring support, code analysis support, decent IDEs are hard to come by.
  • While its getting easier and easier to do Continuous Integration, Test Driven Development and Continuous Delivery with modern software programming environments, it't not getting much easier to automate the database portion of your app. Many teams struggle with automating migrations, coordinating database versions with software versions and other struggles. It doesn't help that often to automate the deployment of your application to several environments, you'll have licensing issues to work through, which will make Continuous Integration and Continuous Delivery harder. This is compounded by the fact that there is not a rich ecosystem of testing frameworks for SQL, which is going to make it harder to develop a test suite that gives you confidence that you haven't broken your app.

More and more, many teams are considering the database an option for persistence; not for logic or functionality. This is leading to better architecture and applications that are easier to manage, test, and deploy.

share|improve this answer
    
Quit writing my answer because this has it covered! –  Chris Pitman May 7 '13 at 23:21
    
I have downvoted this because I disagree that moving to a non relational database will solve or help any of the problems, and also disagree that having business logic in well written stored procs is hard to understand or support. But I do agree that TDD and CI is difficult with the database portion of your app, but it's definitely possible, I do it and have done so on large systems. –  LachlanB May 8 '13 at 1:31
    
@Kyle: Thanks for the response. I tend to agree that BL should be in the application as possible. To confirm, in this instance, the calculation of performance numbers for some user defined time range should also be done on the application side (e.g. in a OO programming language using ORM to get daily prices data (i.e. base data) from the database) versus using a stored procedure. Do I have that right? –  James May 8 '13 at 19:00
    
In MongoDB, or other NoSQL technologies, your application logic can send a function to the repository that is then executed in the repository. This means that the operation happens where its fastest (in the repository), while keeping the business logic where it belongs (in the application). –  Kyle Hodgson May 10 '13 at 19:23

TLDR

  • Storing your queries separate from the reports they drive will create a mess in your database; if you are going for plain SQL queries, embed them in the report.
  • Consider using SSAS (SQL Server Analysis Services), or SSRS Report Models (sometimes called a semantic model), rather than plain old SQL queries. Not always possible, but generally performs better.
  • Don't put things like calculation of performance figures in procedural code like Java, or C#, it is better to make them part of a view on top of your database (such as the SSRS, or SSAS features mentioned above), they perform better, and logically sit inside your reporting stack.

Full Answer

The biggest disadvantage of backing your reports with stored procedures is that the report, and the query to fill it are stored separately, and whilst the report tells you what query it depends on, the stored procedures do not tell you which assets depend on them. Now, you might start off with the intention of having a strict 1-to-1 mapping of stored procedures to reports, but in my experience, somebody will try to be clever later on and layer on stored procedure on top of another, and you end up with a web of dependencies (I've spent the last 6 months on a project to slightly improve the situation at my workplace). If you maintain rigorous documentation you can deal with this, but let's face it, documentation is hard, and annoying, and almost always out of date.

A better solution to using stored procedures would be to embed the query straight in the report, this way the report and the query are coupled to each other, they reside together and you don't get other dependent assets latching on without your knowledge. I know that this flies in the face of the "conventional wisdom" about code reuse, but SQL is not code, and the dependency chains can not be determined anywhere near as easily is in compiled code.

Another disadvantage of stored procedures is that they aren't actually the best way to drive reports, especially when you are talking about calculating aggregates over a period of 25 years. SSAS (Sql Server Analysis Services) is specifically designed for this kind of querying, and it facilitates a much more user-friendly method of building queries and reports (empowering users to build reports is brilliant). Furthermore, it allows the same data to be queried through excel as pivot tables etc (which people love). Microsoft's whole direction with reporting is to move the querying out of the database, and into the SSRS/SSAS layer. You won't always be able to produce all your reports this way, and will have to fall back to a hand-written SQL query at some point, but keep it in the report.

Now, as for the question around whether the "business logic" should be in Java. If you're talking about calculations for various statisics, or KPIs, essentially static functions over the data, rather than anything that deals with user interaction, then no, you shouldn't stick those things in code. They should be a part of your reporting stack. In Microsoft-land the best place to put these is in a SSRS Model, or an Analysis Cube (in fact the 2012 products have a replacement for the cube which gives you the same concept without needing to have batch-processing step, so it works off live data).

share|improve this answer

I wouldn't be too worried about vendor lock-in. Moving to a different database would be a very large project for anyone and would be rare.

Arguments for putting all business logic into stored procs:

  • For very heavy duty data processing, you need very well optimized code. This is what databases excel at - it makes a lot of sense to write very finely tuned SQL and pay a lot of attention to indexes and query plans etc. This will undoubtedly become in issue if you don't use the correct tool/technology for this heavy lifting.

  • Databases (eg sql server) will performance tune themselves based on your query using statistics. This is a large benefit.

  • All of the logic will be in one place and (if well written) should be relatively easy to maintain, edit and deploy and script into source code control.

  • Lots of people know how to write and maintain procs, it's a very mature science.

Cons:

  • Stored procs can grow into an unmaintainable mess if not written carefully. While this is true of any language, it's especially true of SQL server stored procs.

  • Doing any logic that isn't directly database work (eg copying files around) isn't what a database is designed for. Maybe it has that functionality, but in my opinion you definitely want an application that will do this - when it comes to FTP'ing files around, unzipping etc you really want the full control of application code. Maybe a database can do this kind of thing, but I have very strong doubts whether it can do it well, provide good error handling etc. What if you need to unzip a file that's password protected? What if the file is empty? Network connection dropout? File is locked? What if... etc I'm sure there are lots of use-cases that the DB can't handle.

  • Debugging large stored procs can be very, very difficult. If things start going very wrong then you're in for a world of pain.

  • It can be very easy for stored procs to get out of sync between systems (Dev DB, UAT DB, production DB). This needs to be done very carefully. (Personally I script all of my procs and as part of my builds/releases I run in the procs into their respective databases, great once it's all setup).

  • (Here's an opinion for you) - SSRS is a pain in a butt. I hate it and so do most of the devs that I know, no-one wants to work when them. Try to hire someone to maintain SSRS stuff will be much harder than hiring a Java developer.

If it was me, I'd write a Java application to do the file handling, and for the heavy duty data processing use stored procs, if necessary. Best of both worlds and no SSRS :)

share|improve this answer
3  
I see a cyclic argument here. Paraphrasing: "Moving to a different database would be a very large project and would be rare, so go ahead and put everything in the database". But putting everything in the database makes changing database vendors a large project and thus doesn't happen very often. Chicken and egg come to mind. The less logic you have in the database, the less of an issue moving vendors is, the more it (could and would) happen... (On top of that you have the issue of using database specific features for your business logic, increasing the scope of any project to change...) –  Marjan Venema May 8 '13 at 7:19
    
@marjan you are completely right :) however in my meagre defence I would say that database lock in occurs for lots of reasons, not just stored procs. If you were genuinely worried about that, then sticking to ANSI SQL should fix that for you. But even still, moving to a different DB would still be a big project, just like changing your underlying OS or web server software. –  LachlanB May 8 '13 at 14:26

I think it depends on how many reports you are running. If just a few then it makes sense to just use embedded queries because it will be alot faster and cheaper to complete the work. If your building many reports than stored procedures can be argued for as it reduces the need to write sql code over and over again and just use procs. Also if database structure is likely to change it is much easier to change it in one stored proc over combing through all your reports looking for references.

Another alternative that should be noted as well is using shared datasets. This could be a compromise between the two choices you are looking to compare and contrast that could possibly solve your problem. Shared datasets are reusable making them an optimal choice. Only problem with shared datasets are that are only realistic for reports without parameters. If using a report that has search parameters it is best to use stored procedures as these can be passed in.

I would suggest a hybrid approach based upon need. Always start with an embedded query to do the first report of its kind. If other reports are needed based upon the same query using a shared dataset or stored procedure based upon the parameters that the report needs.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.