September 15, 2015 at 12:44 pm
Hi all:
Here is the setup...
Standard Order header / Order detail records (1-m). Each order detail has a product id, order detail and ship detail.
Product info is split into 2 tables - product and product_Stat. Product has the static data (like description) and product_stat has dynamic data (like onhand & onorder).
Quandry:
I have a field in product_Stat: onorder. It represents the detail for an item currently on all open orders. Right now I am trying to manage with triggers. I am debating about turning it into a calculated field. The calculation would be:
select sum( detail)
from order_header
inner join order_detail on order_header.order_header_Id = order_detail.order_header_id
where ship is null and cancel_fl = 0 and product_id = xxx
What cost in terms of speed am I going to incur if I change to this and is there any indexes, views, etc. that would help?
Thank you,
Mike
September 15, 2015 at 1:08 pm
mike 57299 (9/15/2015)
Hi all:Here is the setup...
Standard Order header / Order detail records (1-m). Each order detail has a product id, order detail and ship detail.
Product info is split into 2 tables - product and product_Stat. Product has the static data (like description) and product_stat has dynamic data (like onhand & onorder).
Quandry:
I have a field in product_Stat: onorder. It represents the detail for an item currently on all open orders. Right now I am trying to manage with triggers. I am debating about turning it into a calculated field. The calculation would be:
select sum( detail)
from order_header
inner join order_detail on order_header.order_header_Id = order_detail.order_header_id
where ship is null and cancel_fl = 0 and product_id = xxx
What cost in terms of speed am I going to incur if I change to this and is there any indexes, views, etc. that would help?
Thank you,
Mike
Anytime you are trying to maintain calculated values via triggers you are in for a long and painful process of working out the kinks. At some point those values are going to get out of synch. Why do you need to always have this aggregate available? Can't you simply calculate it when you need it or when the order is created?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 1:12 pm
We use the onorder to help in other areas of calculation like shipability - is the order shippable based upon current orders. That is why I am considering a calculated field.
Mike
September 15, 2015 at 1:16 pm
i would use a view that does the calculation from your post above, and then you can poll the view instead of the base table. that is the easiest route, and would never get out of sync with reality the way triggers inevitable do.
Lowell
September 15, 2015 at 1:22 pm
Lowell (9/15/2015)
i would use a view that does the calculation from your post above, and then you can poll the view instead of the base table. that is the easiest route, and would never get out of sync with reality the way triggers inevitable do.
Given the requirements I absolutely agree with Lowell here. This way your data is always correct. Triggers will slow down all CRUD operations and they will get out of whack at some point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 1:34 pm
What is the difference between using a view and creating a calculated virtual column?
Mike
September 15, 2015 at 1:43 pm
mike 57299 (9/15/2015)
What is the difference between using a view and creating a calculated virtual column?Mike
The main difference is that to use a computed column you would have to create a scalar function. This adds complexity, and possible performance issues, when it probably isn't needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 1:54 pm
Sean Lange (9/15/2015)
mike 57299 (9/15/2015)
What is the difference between using a view and creating a calculated virtual column?Mike
The main difference is that to use a computed column you would have to create a scalar function. This adds complexity, and possible performance issues, when it probably isn't needed.
That is my main issue. How much of a hit for a scalar function. The field is currently in use many places and changing all the references from the table to a view is a large time investment. It would be easier for me to change the underlying data structure than to change all application and web code -- providing the hit is small or not at all.
Thanks,
Mike
September 15, 2015 at 2:02 pm
mike 57299 (9/15/2015)
Sean Lange (9/15/2015)
mike 57299 (9/15/2015)
What is the difference between using a view and creating a calculated virtual column?Mike
The main difference is that to use a computed column you would have to create a scalar function. This adds complexity, and possible performance issues, when it probably isn't needed.
That is my main issue. How much of a hit for a scalar function. The field is currently in use many places and changing all the references from the table to a view is a large time investment. It would be easier for me to change the underlying data structure than to change all application and web code -- providing the hit is small or not at all.
Thanks,
Mike
For a single row, the hit is barely measurable. For 10,000 rows it can be extremely significant. Of course since you say you are currently trying to manage this with triggers you are likely to enjoy slightly better performance. Make the computed column persistent. Then do it on your test/dev system and test the performance. There really isn't a way anybody here can predict that because there are too many unknown factors around your tables that we just don't know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 5:17 pm
mike 57299 (9/15/2015)
The field is currently in use many places and changing all the references from the table to a view is a large time investment.
Would it be an option to rename the table (to, say, MyTable_V2) and then put a vIEW in place of the original table (with suitable calculation for [onorder])
You'd need to check that UPDATE / INSERT are OK - should be fine for a "simple" view ... but failing that you could put an INSTEAD OF trigger on the view - at which point it starts to look less attractive, I must admit! But we have done that in the past in this sort of situation - usually we had "Other stuff" that we also managed to wangle at the same time, which made the effort on INSTEAD OF trigger worthwhile.
September 15, 2015 at 7:42 pm
Kristen-173977 (9/15/2015)
mike 57299 (9/15/2015)
The field is currently in use many places and changing all the references from the table to a view is a large time investment.
Would it be an option to rename the table (to, say, MyTable_V2) and then put a vIEW in place of the original table (with suitable calculation for [onorder])
You'd need to check that UPDATE / INSERT are OK - should be fine for a "simple" view ... but failing that you could put an INSTEAD OF trigger on the view - at which point it starts to look less attractive, I must admit! But we have done that in the past in this sort of situation - usually we had "Other stuff" that we also managed to wangle at the same time, which made the effort on INSTEAD OF trigger worthwhile.
Was kind of thinking of the same thing. This is the time when you hope you don't have prefixes on your table names because suddenly you now have a view named tblOrder. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 8:15 am
Sean Lange (9/15/2015)
Lowell (9/15/2015)
i would use a view that does the calculation from your post above, and then you can poll the view instead of the base table. that is the easiest route, and would never get out of sync with reality the way triggers inevitable do.Given the requirements I absolutely agree with Lowell here. This way your data is always correct. Triggers will slow down all CRUD operations and they will get out of whack at some point.
I don't see why triggered values should "get out of whack", providing they use the same method as a view would to do the total. That is, don't try to adjust the total up or down by individual detail row values, just recompute the total from all the detail rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2015 at 8:21 am
I think it is often easier to adjust the formula if it is in a view. For example if the calculation method for Quantity-on-Hand, taking into account "reserved stock", has to change because some twit in management has dreamt up more ways of "reserving" stock.
But that apart we prefer Trigger because the figure is instantly available and, in our case, we have always had far more Queries than Updates so the "cost" of the at-runtime-calculation has always looked expensive. That won't be the case for everyone / all instances though.
September 17, 2015 at 7:12 am
Hello,
We are supporting a system that has all the main info about each product (quantity onhand, quantity ordered from suppliers, quantity ordered by customers etc.) stored very similarly as described in the original post. There is a special table, which holds this type of data about products and the values are maintained by triggers - any time someone orders 5 pieces of a product, trigger adds 5 to qtyordered. Over more than 10 years of extensive use of this system and several millions of orders, the only "out of sync" issue appeared when someone manipulated with the triggers in very bad way (DISABLE TRIGGER ALL, do something, ENABLE TRIGGER ALL).
Maybe we were just lucky, but in any case I wouldn't want to calculate these amounts any time I need to display them. They need to be displayed pretty often, on many rows, and with 100k products and many millions of rows in the order detail table such calculation is rather costly and slow.
So my opinion is that if you have lots of data, and need these aggregate values often, then trigger can be helpful and I would not recommend changing it.
In another situation, a view would be better solution... you have to consider your environment.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply