Couple of Tips for an External Activator
I recently had to review a solution that was designed to keep an Oracle database up to date with changes made within a SQL Server database. The solution was implemented by using CDC, Service Broker and an External Activator to process the messages. Within this post I would like to share a couple of tips that I gave to help improve the efficiency of the External Activator process. Tip 1: Avoid the temptation for checking to see if there are messages to process. Especially at the starting of the External Activator, as the programme is only launched when there are messages to be read. The only way to check if there are existing messages is to run a select command doing a count on the queue. This is a wasted call to the database server and depending on the number of messages within the queue this could be an intensive query to run . Also attempting to read an empty queue does not cause an error within SQL The best way to check if there are still messages to be processed is by using