Using stored procedures for calculations.

Question

I am currently working on a project that will store specific financial information about our clients in a MS SQL database. Later, our users need to be able to query the database to return data from the clients based on certain criteria (eg. clients bigger then a certain size, clients in a certain geographical location) and total it to use as a benchmark. The database will be accessed by our financial software using a script.

I am currently writing the stored procedures for the database. What I am planning on doing is writing several different stored procedures based on the different types of criteria that can be used. They will return the client numbers.

The actual question I have is on the method of retrieving the data. I need to do several different calculations with the clients data. Is it better practice to have different stored procedures to do the calculation based on the client number and return the result or is it better to just have a stored procedure return all the information about the client and perform the calculations in the script?

Performance could be an issue because there will be a lot of clients in the database so I want the method to be reasonably efficient.

Answer 1

I think a lot of this has to do with the data involved and the operations that you are doing. I typically find that when doing calculations that reduce the size of the return from the DB (Groupings and aggregates) that it is much more effective to do it in the DB. As you start to do other calculations it isn't as clear cut though.

Answer 2

In most cases like the ones I think you're describing it's more efficient to use stored procedures, however there are edge cases where it's not. The best answer would be to try it both ways and do some load testing to determine for certain which method is most efficient.

Answer 3

Interestingly, the data warehouse folks do this all the time. They often use the simplest possible SQL (SELECT SUM/COUNT... GROUP BY...) and do the work outside the database in report-writing tools.

I think you should get a copy of The Data Warehouse Toolkit and see how this can be done in a way that's quite a bit simpler. more flexible and probably more scalable.

Answer 4

SQL Server is a Relational DataStore. I suggest one Stored Procedure per calculation, which does the same calculation for a client. Just pass in the ID as a parameter to get the client's specific result.

"Performance could be an issue because there will be a lot of clients"....this depends on how the tables are normalized and indexed. Don't go indexing every column unless you fully understand what indexes really are.

Edit ~ Look into your calculations as well. Some can be offloaded to front end clients.

Answer 5

It sounds from your question that the most efficient way would be to do all the calculations in the SP and return the single(?) result to your script.

If you do decide to do the processing in the script you should make sure that you're limiting the data you retrieve to only that is necessary and make sure that you're making as few round trips to the database as possible.

Answer 6

It really depends on the operations. It is quite possible to have these things as calculated columns in the database, have them pre-calculated in views or SPs (or use UDFs), have them calculated separately and stored during the ETL or a summarizing phase or let the client do it.

I would avoid letting the client do anything unless you know you can control the calculation consistently, so that they don't make a mistake (report writers who all do the work independently is a recipe for disaster), and especially if the calculation rules might change.

Answer 7

If the business rules that determine the computed result are prone to change, don't code such in stored procedures. A better place to do such would be the controller (C in MVC). The rules would be pluggable and easy to change.

Also from a scalability point of view, duplicating the app-servers is not as expensive as duplicating database servers.

That doesn't mean that stored procedures cannot be used though. Report generation would be one ideal candidate for computing done using stored procedures.

Answer 8

Consider if you need to havea data warehouse. A database optimized for reporting is very differnt from a database optimized for data entry.

Answer 9

Everything you say leads me to think you should consider warehousing your data. If you find yourself writing complex joins against an OLTP system and needing additional calculations (and it sounds like you are), denormalizing your data and storing the precomputed aggregations in a warehouse will make your life a lot simpler.

Despite the enormous amount of books and products that everyone seems to want to throw at you, if your problem space is discrete and your source data is well managed, the task is often trivial.