Hi, I have a trigger on a table which calclates an average based on records inserted into a table. The average is calculated based on one key field in the record(s)
So, for example, if I insert a record with key = 'A', every time I insert a record, I calculate the average for all records with key =- 'A'. There are times when there could be several hundred records with key = 'A'. As the number of records with like 'keys' increases, trigger performance suffers terribly... and all users are subjected to degraded response time. I have observed the performance 'hit' in Profiler... trigger duration can exceed 400 ms... very bad.
I have tested a change to the S/P that does the inserts. By calculating the average in the s/p using the 'Nolock' locking hint, I pretty much conquer the issue. Problem is, I don't trust 'Nolock'. It shouldn't be a problem because these records are almost NEVER updated or deleted.... so I'm always working with the most recently inserted record (which is the one just done within the stored procedure.
Any ideas on alternative approaches? I'd really like to hear what you guys have done in similar situations.
