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.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

View more solutions