I am currently working on an email application where I have to monitor the table for any new record inserts and send email notification for each row. Right now the changes (inserts) will be at low rate but eventually it will become more.
Initially I was thinking about using a SQL Server job to query every 3-5 mins and then I looked into the SqlDependency but I am not sure whether SQL dependency / query notification will be the correct option for my requirement.
If there's an insert every minute then I will be polling the table every 1 min vs every 3-5 mins. I understand using SqlDependency I can get the notification instantly and send email notifications but I don't want that to become a performance impact by polling very often. Eventually there will be 10K inserts within a minute and not sure about the congestion between the other job and this SqlDependency.
I am thinking about going with the SQL Server job polling at certain predefined intervals. Any thoughts or suggestions? I am pretty sure someone should have faced the same situation before. Appreciate your feedback. Thanks
One more question During peak periods it's Ok to poll the table every one minute or so after query notification but what happens to the records that gets inserted during the processing (select/send emails/update). I am assuming it will not get notified until next record gets inserted correct?
