4

I was wondering where to do/put calculation of fields to be calculated from other data. Yes, I am talking about computational field. Also the computed data needs to be saved in database. So.. Weather it should be done at Database or by some external code like a windows service. ?

Let me put my need/scenario:

  • I am developing a web application to disseminate data received by multiple instruments/sensors deployed somewhere in field.
  • A windows service in C# is developed and deployed at Microsoft Windows SQL Sever 2008 R2 to receive, format and insert data into a table (Let say tblSensorData) in SQL database deployed at same machine.
  • A Insert and Update trigger is implemented on 'tblSensorData' and is used to call a stored procedure which computes 3 other numerical values from received sensor reading and insert a row in another table (Let say 'tblComputedFields').
  • Therefore, every new raw data received have a computed field row generated in database and hence I have consistency in this way.

I did some search on good or bad practice of dealing with computational fields and people argue to do at applications and some at database. But couldn't find a precise answer depending upon my current scenario.

One thing is for sure, if we do the same task of computation aside from database in any external code it should be Insert Event/Trigger based to know when to do the calculations and for consistency of data in database.

Any suggestion would be appreciated about where do the same task outside database. ?

  • 3
    What would keep you from calculating these values before inserting them in the SQL database? – Jonathan van de Veen Oct 11 '17 at 12:37
  • Well.. Actually nothing. Windows service is doing the inserting part and hence a insert trigger is invoked which call a stored procedure to compute three other fields. I just made Windows service to be assumed to just format and insert RAW Data from sensors so in case of changing its code, we don't have to put sensors on wait when redeploying the service and to avoid any loss of data. – w_billa Oct 11 '17 at 12:48
  • 1
    I think you have a good use case for doing it in the database. – Pieter B Oct 11 '17 at 13:30
  • 2
    In SQL Server, for example, you can have a computed column be persisted via the PERSISTED keyword. Such columns have their values updated when any columns that are part of their calculation change. The advantage of persisting a computed column is both performance for complex computations, and that it can be indexed if you use these as criteria in queries. – Erik Eidt Oct 11 '17 at 19:16
3

If the calculation is based only on the the input record and held within the [same] stored record and the calculation is relatively light-weight, then create a view that sits on top of the table and calculate the fields on demand. Complex calculations will slow the view to an unusable crawl.

If the calculation is intensive, or time-sensitive (i.e, it has to be performed at time the data is received) then use triggers to calculate and store the result would be better. Again, if the calculation is intensive, consider writing the raw data (plus a "received" timestamp for that time-sensitivity) to an ancillary table and have your service poll this, do the calculations and store the proper result.

  • Thank you for your suggestions. I agree with both Phill W and Walfrat. I guess in this particular case, it depends upon how intensive are the calculations. Luckily, mine are light weight so i would prefer to do at database. I would conclude here for others: - Dealing with computational fields depends upon your goal. - It can be done both at Database or outside database. - Complexity of computation/Data processing is the key to decide where to do the calculations. - Performance is important factor to be considered apart from your goal. – w_billa Oct 12 '17 at 8:12
4

They can be :

  • In database, using triggers to enforce computations.
  • In database, using views, it naturally enforce read-only attributes. Futhermore whenever the calculation changes, you don't have to update all the stored outcomes. Finally, lot of people try to avoid having too many triggers in a database.
  • In application, if the way of calculating the datas might change according to some criteria it might be easier to handle this at this level. If you do it at application layers you must ensure that the field is read only.

How to choose :

  • If you have only one application that might write this computed data, you can go for the application way.
  • If you have multiple application that might write the computed data, go for database solution.
  • 2
    One thing in favor of a view, is that whenever the calculation changes, you don't have to update all the stored outcomes. That and I personally avoid triggers if I can. – Pieter B Oct 11 '17 at 13:32
  • @PieterB I have updated the answer – Walfrat Oct 12 '17 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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