| Author |
Topic |
|
phenreid
Starting Member
29 Posts |
 Posted - 2010-11-22 : 12:42:46
|
| I have a situation where I need a field in my master table to keep track of the number of recs in my child table that have Status='A' (active). A pretty common requirement.I can do it with a view several ways. One involves creating a view of the PK + active counts and left joining to it because I need it to show zero of there are no active child recs.The other uses a DT/Subselect to return that field. Both operate fast and accurately.But, the problem is that with either left join or DT in a view, I can no longer give it a unique index and that is a requirement for my application that requires one unique index in order for the table to be updateable by my front-end app.Therefore, I am going to add an ActiveCount field to my parent table and use a trigger to update it. I hate to use a trigger to "post" a count that can be obtained with a view -- but I do need to be able to index the view for multiple reasons.As far as I know, if you want to add a DT or left join to a view, it is no longer indexable and there is no way around that. Several of my queries suggest the use of indicies on this view -- not just the front-end app.So, where you want to index a view and also include a DT or left join in the select, this is a good example where trigger is justified, true?(I realize that this view vs. trigger trade-off depends on how much activity the trigger will get -- in this case it is quite reasonable.)Thx. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 13:48:14
|
Actually phenreid is correct. I think you missed the "Indexed View" part Brett.  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-22 : 18:20:33
|
| Can you have a Filtered Index on a VIEW to keep the NULL rows out of the index? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 19:29:12
|
| Filtered indexes are only supported on tables, so no, can't do it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-23 : 01:09:31
|
quote:
Originally posted by russell
Filtered indexes are only supported on tables, so no, can't do it.
I feared that would be the case.  Next version maybe  |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-23 : 08:43:15
|
| OK, random thought here, and I may not be understanding the requirements completely, so bear with me.My understanding is that you need to keep a running total of records that have a specified status (currently, that status is 'A', but in my experience, that could change or be added).Why don't you create a separate table that gets updated, either by sproc(s) or trigger(s)? One entry for each status code, and a current total. You could also store the last amount, date updated, etc. in there as well. |
|
|
|
phenreid
Starting Member
29 Posts |
Posted - 2010-11-23 : 14:30:49
|
Without describing the whole application -- I have to join three master-record (non-transactional) tables into a view that is updateable by my UI. I need to see the number of records in one of the tables that has Status='A' in another table, in my view somehow.I realize it could be out-of-date when presented, but that is a non-issue for my situation. Small number of users; not a lot of simultaneous processing -- if it were changed while the view was on someone's screen that is no big deal.I hate to keep a "posted" count somewhere with a trigger when I can just get the count. BUT, for UI to see the view as an UPDATEABLE recordset it has to be WITH SCHEMABINDING, and has to have one unique key. Also, in various execution plans other indicies were suggested on this view that really help a lot.However, to get the count (which could be zero) I need some sort of subselect or a left join. Either of these make the view unable to index. I even tried creating a view with the coutns that has all the records so I could do an inner join to my main view, but even that prevented indexing.Purpose of this post was to confirm this restriction is real and there is no workaround; therefore, I added a field to the appropriate table (I called it ActiveCount) and I keep it current by trigger. I did that, but wanted to make sure I did not use a trigger unnecessarily. Seems odd to me that I'm keeping a count in a table field that is readily available with an on-the-fly COUNT(*).By the way, in my trigger, I only want to update ActiveCount if the status field was actually updated, or if the entire record was inserted or deleted. I couldn't immediately find a way to do this because IF UPDATE(status) only works for updates. IF Exists (select * from inserted) tells me if there was a new record. But what about deletes? How do I tell that it's a delete with no update, if there's no corresponding record in INSERTED by joining INSERTED To DELETED? Is there a function to determine if the trigger action is just deleted? Is it: "IF Exists(select * from Deleted) And UPDATE(status)=false": so the trigger IF would be: If UPDATE(status) or EXISTS (Select * from Inserted) or (EXISTS (select * from Deleted) And UPDATE(status)=false) UPDATE TableA SET ActiveCount = Select count(*).... I don't want to run an update statement for other fields being updated. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-24 : 04:46:53
|
"I hate to keep a "posted" count somewhere with a trigger when I can just get the count"My take on that is it depends on the number of Reads to Writes, In Forum Software continuously dynamically calculating the number of Posts a user has made would be prohibitive - there are millions (some exaggeration!!) of Reads for each Write that would require incrementing the post count.However, if you have a running-total table/column you have to be sure it is correctly maintained. What happens if the moderator deletes a post? Calculating it doesn't have that issue of course.Maybe your scenario has more Writes than Reads - in which case Calculation might be better.Calculation, IMHO, also falls down when the number of rows to Count is large. It will take time EVERY time, and scale badly."Seems odd to me that I'm keeping a count in a table field that is readily available with an on-the-fly COUNT(*)."If you had a billion rows to count would you still think that? (I wouldn't !!). At that point you would be creating the trigger for Performance Reasons. I don't find it objectionable to do it in order to have a NOT NULL column in the view (and the Performance Benefit might well be a bonus - even if that is down-stream). Either way ... I can talk myself into it!"However, to get the count (which could be zero) I need some sort of subselect or a left join"Two VIEWs? Your query INNER JOINs to the first and OUTER JOINs to the second, both can be indexed. Might plkay merry hell with trying to have an update-able view though."IF UPDATE(status) only works for updates"And not all that usefully even then. "UPDATE(status)" is TRUE if the [status] column was included in the UPDATE statement, regardless of whether its value actually changed. It obviosuly has merit to weed out updates that were NOT on the status column (with very low processing cost), but you still need a "Did the value really change" type test.If this is an UPDATE trigger there will be an identical number of rows in INSERTED and DELETED tables.If the trigger is ALSO for INSERT and/or DELETE then there will be an unequal number of rows when either of those two scenarios arrises,Typical route to maintaining a counter where you have to worry about all three of Insert, Update and Delete would be:UPDATE CSET TheCount = TheCount + COALESCE(I.SomeQty) - COALESCE(D.SomeQty)FROM INSERTED AS I FULL OUTER JOIN DELETED AS D ON D.MyID = I.MyID JOIN MyCountTable AS C ON C.SomeID = COALESCE(I.SomeID, D.SomeID) You are dealing with count of records, I think, rather than SUM of a moving total - such as Stock level - so you probably just needSET TheCount = TheCount + CASE WHEN I.MyID IS NULL THEN 0 ELSE 1 END - CASE WHEN D.MyID IS NULL THEN 0 ELSE 1 ENDthen you want a WHERE clause to stop this updating any rows where there will be a NETT effect of nothing. Perhaps this?WHERE (I.MyID IS NULL OR D.MyID IS NULL)which will only update TheCount if there is an INSERT of DELETION but not an UPDATE. In your case its probably a bit more complicated in terms of NOT updating on an UPDATE where STATUS=a and it has NOT changed in the update, but updating for INSERT AND Status=A, DELETE AND Status=A or an update where either the INSERTED or DELETED data is Status=A, but not both. |
|
|
|
|
Trigger or View If Need To Index View