[thelist] MS SQL Server - T-SQL

[Tab Alleman]

barry sweeney wrote:

> One question, I understand how this approach will ensure that there
> is never a synchronisation issue, but from an efficiency point of
> view, isn't it better to update one field once rather than
> 'recalculate' it everytime it's displayed?


Seems to me that that could be a pretty give/take-ish issue:

Say that you have a static column in your topics table that stores the
NumberOfReplies.  And everytime the reply table is INSERTed or UPDATEed,
you fire a trigger that figures out which row to update in the Topics
table, and updates the NumberOfReplies column for that table.  

Doing this has a relatively sizeable overhead, but you only do it when
the replies table is changed, and not everytime you query the topics
table.  

Doing the calculation each time you query the topics table will have a
much smaller overhead, but, well, you'll be doing it everytime you query
the topics table (excuse the redundant sentence structure, please...it's
getting late).

Soooo, if you query the topics table A LOT more often than you INSERT or
UPDATE the replies table, it might be more efficient to use the trigger
method.  It would have to be a lot though, because SQL Server is pretty
well optimized to do exactly this sort of calculation when you query it.
The overhead is really very small.  Sorry I don't have any benchmark
figures to share, but they're probably Out There Somewhere.

HTH,
Tab