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.

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.

share|improve this question
    
Why would you need two database connections? The stored procedures can always share the same connection. –  System Down Apr 16 '13 at 23:58
1  
    
See edit... I elaborated on the "2 db connections" comment –  jwatts1980 Apr 17 '13 at 13:47
    
If the computations are simple, why not use one table and perform the computations when the report is generated? It seems you are denormalizing your database for little gain. –  kevin cline Apr 17 '13 at 15:34
    
@kevin cline: transforming the data going into the database means it gets stored based on the computations used at that moment. Changing the calculation on the presentation side would affect all historical reports as well (unless additional steps were taken to separate the calculations). This too has pros and cons, but the decision has already been made to calculate prior to storage. –  jwatts1980 Apr 17 '13 at 19:46
add comment

4 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
I believe there are many different angles to look at this problem from, but I don't think these are the strongest arguments. Briefly - you touch on performance, but moving calculations into the DB adds unnecessarily to the DB CPU load, and CPU-bound performance issues are often difficult to fix. This also bifuricates business logic. Most thick-client apps either don't connect directly to the DB, or can be rolled out rapidly enterprise-wide, making change / deployment less of an issue. Compile-time guarantees and unit testing do a better job of alerting me to breaking changes in the DAL. –  Daniel B Apr 17 '13 at 6:30
    
TL;DR: the round-trip part is one of the main differences, but it's a complicated trade off involving the nature of locks on the DB and horizontal / vertical scaling. The software engineering arguments are fairly subjective, and many (myself included) would heavily prefer not to have business logic in the DB. –  Daniel B Apr 17 '13 at 6:56
    
+1 for the thought out response. I updated my post with a little more detail related to the type of application I'm working on. The service application could potentially be scaled to multiple remote servers later on, so updating the application is a concern. And, at this point in the dev process, I think this is the only place where the raw data will need to be transformed before being stored, so it's the only place where this calculations question would come up. –  jwatts1980 Apr 17 '13 at 14:04
2  
In this case, because of the simplicity of the calculations, the difficulty required to update the logging system to support multiple s-procs on one connection, and the more elaborate process required to re-install the application should a calculation change, I'm going to put them in the stored procedure. –  jwatts1980 Apr 17 '13 at 14:15
    
great to have stored procs do part of the work unless you might run in to db issues later (easier to scale app then db); change db (though all have stored procedures but might need porting.) –  tgkprog Apr 17 '13 at 14:31
show 3 more comments

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.

share|improve this answer
4  
That's funny, because I was thinking about it exactly the opposite way... that putting the logic into the stored procedure would mean I could change it there instead of having to update the program. In this case, the program would be a Windows Service running on a remote server that would require re-installation to effect changes. –  jwatts1980 Apr 17 '13 at 13:50
2  
+1 me too, though I've worked at a number of places that prefer the opposite, because it's really easy to make on the fly changes to stored procs instead of a software release. –  Ozz Apr 17 '13 at 13:51
1  
Re complex calculations: we're talking 5 equations on the level of "A + B", "C - D", and "(C - D) / E". –  jwatts1980 Apr 17 '13 at 13:52
add comment

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.

share|improve this answer
    
Yeah, that seems to be the popular theme in CS programs these days. One of the problems, though, is that it leads to widespread campaigns to use tools like LINQ to SQL. Which in turn leads to this: samsaffron.com/archive/2011/03/30/… –  Craig Nov 3 '13 at 19:40
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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