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

You can do any calculation in stored procedure and return data. Interest calculation like stored procedure need lot of calculations.

Any way you have to calculate data with other table data's.

SUM we can do in SP,

Declare @SUMAmount decimal(12,3) 

-- also declare @A, @B etc

Select @SUMAmount= SUM(ISNULL(@A,0)+ISNULL(@B,0)+ISNULL(@C,0)+ISNULL(@D,0))

Select @SUMAmount= SUM((ISNULL(@A,0)+ISNULL(@B,0))*(ISNULL(@C,0)-ISNULL(@D,0)))

As per your requirement you can give condition.

ISNULL is using for check whether data is NULL then return 0. Calculation with Null value is not possible so better give ISNULL condition.

Select A,B,SUM(C),D From TableName
Where SUM(C)>0
Group By A,B,D

Here Both aggregate and non aggregate functions are there so you have to use Group By. You can get values as per your condition eg: "Where SUM(C)>0". Having also you can use here after Group By.

 Declare @TotalNoofDays  int
 @TotalNoofDays = DATEDIFF(d, fromdate, todate) 

Using for find number of days using this function.

You can use condition like,

if @DueAmount >=0
BEGIN
IF @DiscountFlag = 1
BEGIN
SET @DueIntAmount   = 0
END
ELSE
   BEGIN
     SET @DueIntAmount  = ((@DueAmount*(@IntRateOnDue/100))/365)*@NoofDays
   END
  SET @ExcessInterestAmount = 0
END
ELSE
BEGIN
SET @DueIntAmount   = 0
SET @ExcessInterestAmount = ((@DueAmount*(@IntRateOnDeposit/100))/365)*@NoofDays
 END

Like calculations you can do in Stored Procedure.

If you want to do calculations with number of records one by one then you can use temporary table while loop..

 Create Proc NewLearningProcedure
 (
   @Name Varchar(50),
   @Date DateTime
 )
 AS
 Begin

Declare @Temp Table
(
   ID int Identity(1,1),
   Name Varchar(50),
   Date DateTime
)

Insert Into @Temp
Select @Name,@Date

Declare @i int
set @i=10

While @i>0
Begin
  Insert Into @Temp
  Select @Name+CAST(@i as varchar(50)),@Date

  Set @i=@i-1
 End


 Select * from @Temp


 End

Like you can do anything with Stored Procedure.

Answer 2

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.

Answer 3

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

Answer 4

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 5

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 6

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 7

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 8

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 9

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 10

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.