July 15, 2015 at 11:13 pm
In general....
Is it better to do server-side rules and processing or let the app do it?
For example....
I want to update a sub-total field on the order header. It is the sum of qty * price for all detail lines. Would it be better to let app do it or set up an update trigger on the order detail record?
Thanks!
July 16, 2015 at 2:40 am
Put it in the database. Putting it in your app won't help when other apps, spreadsheets, MS Access, etc are connected to the DB, when the app is changed or replaced, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2015 at 7:23 am
I agree with Gail - for this particular case. The thing is you need to make this decision (often with many others) for each touch point in your application. And your decision tree varies with MANY things too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2015 at 7:48 am
GilaMonster (7/16/2015)
Put it in the database. Putting it in your app won't help when other apps, spreadsheets, MS Access, etc are connected to the DB, when the app is changed or replaced, etc.
+1 ... (and I'm willing to go with + 1 BILLION !!!)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 16, 2015 at 7:56 am
Hmm... in that specific case it sounds like you want to run some kind of batch job to update a field which probably would be better done at the DB level.
July 16, 2015 at 8:16 am
ZZartin (7/16/2015)
Hmm... in that specific case it sounds like you want to run some kind of batch job to update a field which probably would be better done at the DB level.
I would not think the user was speaking of a batch job. That would mean new data or modifications would not be reflected in the totals column. I would think the intent was for transactionally-consistent updating.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2015 at 8:43 am
TheSQLGuru (7/16/2015)
ZZartin (7/16/2015)
Hmm... in that specific case it sounds like you want to run some kind of batch job to update a field which probably would be better done at the DB level.I would not think the user was speaking of a batch job. That would mean new data or modifications would not be reflected in the totals column. I would think the intent was for transactionally-consistent updating.
Hmm... then I would say it depends on the app and the requirements. If the intent is just to let users see the total on a screen there might not even be a reason to store that information on the top level record and just calculate it as needed when viewed.
July 16, 2015 at 8:55 am
Yes, we store the total for the order. I have many small calculations that are done by the app. I want to move them to the DB but I am concerned that it could be a slow down because it is a trigger. Given that I am dealing with a under 10 million detail and 3 million headers (at most)... should I be concerned?
Thanks.
July 16, 2015 at 12:56 pm
mike 57299 (7/16/2015)
Yes, we store the total for the order. I have many small calculations that are done by the app. I want to move them to the DB but I am concerned that it could be a slow down because it is a trigger. Given that I am dealing with a under 10 million detail and 3 million headers (at most)... should I be concerned?Thanks.
For this particular operation the total number of records is of relatively minor consequence. Assuming you are properly indexed updating a header with a total for details should be small effort. What would matter is the FREQUENCY of DML activity. Very high volume activity could lead to locking/blocking issues and increased dirty page and tlog writes that could be problematic. But I am not talking a few updates per second here though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 17, 2015 at 7:26 am
Another perspective: do you actually need to store the subtotal in the OrderHeader table?
This could be computed on-the-fly using views or stored procedures from the actual order details, right? Have you evaluated whether you're re-calculating subtotals so much that you'd clearly benefit from storing and updating them?
This falls squarely in the "it depends" category of answers. If you're thinking of using triggers to maintain the data, you might wind up introducing more waits with a poorly written trigger than recalculating subtotals with a view when needed.
Rich
July 17, 2015 at 7:43 am
What about using a calculated column for the total, instead of using a trigger, if the trigger proves to be performing poorly?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 17, 2015 at 8:33 am
Another side of this is that if for whatever reason you need to change the calculation you're using if you store it on the top level record you would then not only need to update the triggers in the database but go in and clean up all the old data. If it's just a calculation in the app all you would need to do is update the app.
July 17, 2015 at 8:37 am
rmechaber (7/17/2015)
Another perspective: do you actually need to store the subtotal in the OrderHeader table?This could be computed on-the-fly using views or stored procedures from the actual order details, right? Have you evaluated whether you're re-calculating subtotals so much that you'd clearly benefit from storing and updating them?
This falls squarely in the "it depends" category of answers. If you're thinking of using triggers to maintain the data, you might wind up introducing more waits with a poorly written trigger than recalculating subtotals with a view when needed.
Rich
That would still require access data from the child table to update the parent table. Rather brain-dead this morning but even if that is possible it likely won't be (much) more efficient than a trigger. I say much due to the extra overhead of INSERTED/DELETED table creation/population in trigger.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 17, 2015 at 9:22 am
TheSQLGuru (7/17/2015)
rmechaber (7/17/2015)
Another perspective: do you actually need to store the subtotal in the OrderHeader table?This could be computed on-the-fly using views or stored procedures from the actual order details, right? Have you evaluated whether you're re-calculating subtotals so much that you'd clearly benefit from storing and updating them?
This falls squarely in the "it depends" category of answers. If you're thinking of using triggers to maintain the data, you might wind up introducing more waits with a poorly written trigger than recalculating subtotals with a view when needed.
Rich
That would still require access data from the child table to update the parent table. Rather brain-dead this morning but even if that is possible it likely won't be (much) more efficient than a trigger. I say much due to the extra overhead of INSERTED/DELETED table creation/population in trigger.
I think you misunderstood, Kevin, or else I wasn't very clear: I was suggesting that one option was to not persist the subtotals to the database (parent table) at all. Instead, whenever a subtotal was needed, either for displaying or querying, it could be computed on-the-fly from the order details. Again, it depends on the op's needs, but I thought it was worth suggesting.
Rich
July 17, 2015 at 9:33 am
rmechaber (7/17/2015)
TheSQLGuru (7/17/2015)
rmechaber (7/17/2015)
Another perspective: do you actually need to store the subtotal in the OrderHeader table?This could be computed on-the-fly using views or stored procedures from the actual order details, right? Have you evaluated whether you're re-calculating subtotals so much that you'd clearly benefit from storing and updating them?
This falls squarely in the "it depends" category of answers. If you're thinking of using triggers to maintain the data, you might wind up introducing more waits with a poorly written trigger than recalculating subtotals with a view when needed.
Rich
That would still require access data from the child table to update the parent table. Rather brain-dead this morning but even if that is possible it likely won't be (much) more efficient than a trigger. I say much due to the extra overhead of INSERTED/DELETED table creation/population in trigger.
I think you misunderstood, Kevin, or else I wasn't very clear: I was suggesting that one option was to not persist the subtotals to the database (parent table) at all. Instead, whenever a subtotal was needed, either for displaying or querying, it could be computed on-the-fly from the order details. Again, it depends on the op's needs, but I thought it was worth suggesting.
Rich
Another question is how often do you pull up just the parent record without also pulling up the child records?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply