Should I Do Calculations in T-SQL or Program?

Question

I am creating a stored procedure that is logging some data. The data ultimately needs to end up in 2 tables. The incoming data is coming from a JSON string containing 15 parameters, and the data is then logged to the database using the stored procedure I'm writing. I would like to send the data into 1 stored procedure and INSERT it into both tables.

The first table is a raw data logging table. It will be used for debug and troubleshooting.

The second table will be used for report generation. This table requires some simple mathematical calculations to be done on incoming data. For example:

DECLARE @Table2Fld3 DECIMAL = @IncomingFld9 - @IncomingFld4;

I'll have about 8 of these calculations to do to compute the values for table 2, then I'll do an INSERT to save the data.

So my question is, is it a good practice to do these calculations in the T-SQL? Or would it be better for me to create 2 separate stored procedures and do the calculations in my code?

One trade-off I see is that if I do it all in the code then I have to create 2 database connections.

EDIT

I should elaborate on the "2 database connections" comment. The application in question is a windows service that establishes multi-threaded server/client communication. The logging system is asynchronous to the server/client communication. Using that existing system, in order for me to target multiple stored procedures, it would require 2 calls to the logger which would spin up 2 connections to the database.

Answer 1

I prefer to keep plenty (not all, but plenty) of the logic in the database for just a ton of reasons (and without getting into the whole "who needs stored procedures nowadays" argument).

Do this in your stored procedure.

These look like simple computations. Also use a transaction in your sproc to manage the writes to both tables, unless it is okay if your database becomes inconsistent (sometimes the write might succeed in one table, but not the other). And don't listen to the guys who try to tell you that "real programmers write their own rollback code," because that's just silly. :-) The only reliable rollback code is the rollback code inside the database engine itself, and it's easy to use.

If you do these calculations in your imperative code outside the database and use two stored procedures to write the data, you still have to consider utilizing a database transaction unless it is okay for your database to become inconsistent. So now (if you do this outside the database) all of a sudden you're up to at least 4 round-trip calls to the database instead of 1:

1. Begin TX
2. Write table 1
3. Write table 2
4. Commit TX

If the writes are small, it is entirely possible that you'll have more overhead in the network round trips than in the actual work done by the database. Even though you can use the same connection to call both stored procedures (must, if you use a transaction), you still have to make multiple, separate calls to the database server across the network.

Finally, if the logic of the calculations changes, and you're doing those calculations in your code outside the database, then you have to change that code, recompile it and redeploy the updated program. But if you have those calculations in your stored procedure, you can rebuild that one stored procedure without having to redeploy (re-install?) your entire application.

All of these points are exacerbated if we're talking about a thick client desktop app--which you did not specify--because that means you have to re-install the updated app on every workstation, and while you have it installed on some workstations but not on others, you have different workstations doing different things to your database. Also, depending on your source control and build practices, you could be looking at a major operation to go back in time and branch your repository to make some changes in your data access layer, not to mention the potential work involved in merging the changes in the branch forward into the DAL in your main source line.

Do this in the stored procedure, because that is honestly the right place to do it.

Answer 2

I prefer to keep as much of the logic outside the database as possible. So that if the way calculations are done gets changes, I only need to update the code in the program and not mess around with the stored procedures.

I'm not sure how complex these calculations are, but if they do become a bottleneck you can always move them somewhere else.

Answer 3

The primary responsibility of database engine is for storing data and pull them when we need them.

We should put our business logic / manipulation with data in program code. So that it will not dependent on database. It can be unit testable. Better in performence. We can use the full OOP concept in our business logic.

Answer 4

It seems to me that these simple calculations should be done as part of the report and don't need to be stored in a reporting table. This gives the flexibility of letting the calculations change and not having to re-created the data in the reporting table.