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 the RECEIVE command and checking if nulls haven't been returned. If nulls are returned then the external activator process can finished as there are no more messages to process. Another method is to combine the WAITFOR command around RECEIVE command. This will cause the receive command to either wait until the specified time to return the null or a message as soon as it has been received by the SQL Server.
Tip 2: Consider alternative ADO.net query methods.
Within ADO.net there are several methods to execute a T-SQL command, which all have their pros and cons depending on T-SQL command being executed.Also considering your solution requirements there may be a better way of getting the messages out of the database.
If your requirements is that you only need to read one message at a time from the database then it might be useful to consider using ExecuteNonQuery and using output parameters to retrieve the message, types. Using this method can slightly improve the communication between the application and SQL Server.
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 the RECEIVE command and checking if nulls haven't been returned. If nulls are returned then the external activator process can finished as there are no more messages to process. Another method is to combine the WAITFOR command around RECEIVE command. This will cause the receive command to either wait until the specified time to return the null or a message as soon as it has been received by the SQL Server.
Tip 2: Consider alternative ADO.net query methods.
Within ADO.net there are several methods to execute a T-SQL command, which all have their pros and cons depending on T-SQL command being executed.Also considering your solution requirements there may be a better way of getting the messages out of the database.
If your requirements is that you only need to read one message at a time from the database then it might be useful to consider using ExecuteNonQuery and using output parameters to retrieve the message, types. Using this method can slightly improve the communication between the application and SQL Server.
Comments