[INUG-Users] Automation Help

[Alex Greenbank]

Hello,

users-bounces at netcoolusers.org wrote on 23/01/2008 17:21:01:
> > 4.) ACS_Expire
> >      ** Condition: select * from alerts.status where ACSOSGroup = 6
> >                    and ExpireTime > 0 and Severity > 0 and 
Acknowledged = 0;
> >
> >      ** Action: update alerts.status via '@Identifier' set Severity = 
0
> > where LastOccurrence < (getdate - @ExpireTime);
> 
> 
> This being a two part automation has always bugged me. It would be 
> faster to skip the select ...
> 
> update alerts.status set Severity = 0 where ACSOSGroup = 6 and 
> ExpireTime > 0 and Severity > 0 and Acknowledge = 0 and LastOccurrence 
> < (getdate - ExpireTime);

The "insert journal" option is done for each row selected in the trigger.

If you implement that automation as a single update statement then "Insert 
Journal" would have no effect as no rows are selected in the trigger.

And "update via '@Identifier'" means direct access to the row via the 
primary key hash rather than scanning the entire alerts.status table each 
time, so it's almost exactly as fast as doing a single update statement.

That's why it's done that way.

Ta,

-Alex





Unless stated otherwise above:
IBM United Kingdom Limited - Registered in England and Wales with number 
741598. 
Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU