Writing Efficient Subscription Rules
It is very important that the Transact-SQL statements you use in application rules are optimized for rapid execution. Rule performance is key to the overall speed of the Notification Services system. The faster the rules execute, the less time it takes to maintain application information and generate notification data. This is especially true of the notification generation rule.
Actions
To ensure that you make the notification generation rule as efficient as possible, keep the WHERE clause brief, and avoid lengthy arguments.
If you have many OR operators in your WHERE clause, the additional table reads increase the processing time for the statement and slow down the Notification Services system.
Actions and Condition Actions
Specifying indexes on your event, subscription, and chronicle tables can improve rule performance. For more information about defining indexes, see the following topics:
- Defining Indexes for an Event Class
- Defining Event Chronicle Tables
- Defining Indexes for a Subscription Class
- Defining Chronicles for a Subscription Class
SQL Server provides tools and guidelines for optimizing query performance. You are encouraged to take advantage of these resources to design efficient notification generation queries. For more information, see Query Tuning.
See Also
Concepts
Defining Subscription Rules
Defining Event Rules
Defining Scheduled Rules
Defining Actions
Defining Condition Actions