May 21, 2013 at 9:29 pm
I am looking for your advice.
We have a calendar for each organization where the total count of the org events is kept. Each organization can have 1 or more business units. Each business unit can have 0 , 1 or more events.
The count of events rolls up from the business units. The business unit can update or delete these events and the count of the org will change accordingly. All events are kept on an event table.
There are 3 options to implement the count on the calendar:
1. Create trigger on the event table that changes the count of org events on the calendar on event insert, update or event delete.
2. Increment or decrement the org count on every stored proc that performs an insert/update/delete of events.
3. Create a view ( indexed, with (nolock)) that queries the counts of all organizations and use it for display on each org calendar.
For example: if there are 10 orgs, there will be 10 counts. User of Organization 1 will see the count of Organization 1 from the view.
Can you advice which option is the most viable and which is the worst? I'd like to see it work correctly first then fast next.
Thank you very much!
May 22, 2013 at 12:59 am
You dont need to store the count of events, it should be worked out on the fly by a query or stored procedure. You could do it with a view, but it doesnt need to be an indexed view. You should however, index the underlying tables correctly so that the count query is optimised.
May 22, 2013 at 9:16 am
Coriolan (5/21/2013)
I am looking for your advice.We have a calendar for each organization where the total count of the org events is kept. Each organization can have 1 or more business units. Each business unit can have 0 , 1 or more events.
The count of events rolls up from the business units. The business unit can update or delete these events and the count of the org will change accordingly. All events are kept on an event table.
There are 3 options to implement the count on the calendar:
1. Create trigger on the event table that changes the count of org events on the calendar on event insert, update or event delete.
2. Increment or decrement the org count on every stored proc that performs an insert/update/delete of events.
3. Create a view ( indexed, with (nolock)) that queries the counts of all organizations and use it for display on each org calendar.
For example: if there are 10 orgs, there will be 10 counts. User of Organization 1 will see the count of Organization 1 from the view.
Can you advice which option is the most viable and which is the worst? I'd like to see it work correctly first then fast next.
Thank you very much!
I would agree with foxxo. Another option might be to use a computed column.
If you implement any of the three options you propose you will be fighting an uphill battle.
1) This adds unnecessary complexity and is easy to get wrong.
2) This is not a good choice. You now have everything so tightly coupled it is only a matter of time until something gets missed.
3) DO NOT create a view (indexed or not) using the NOLOCK hint. I would avoid that hint for anything unless you truly understand the challenges it brings to the table.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
May 22, 2013 at 5:45 pm
Thank you both! Point taken. I will stay away from with(nolock) hint and will look into computed column.
Personally, I like the view approach. We currently have #2 implemented and need I say that it has been a never ending treacherous road...
I am looking forward to replacing it with the view.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply