CODEWITHSUNDEEP
sqldatabase
user1058553
user1058553

Reputation: 33

Prevent an ASP .NET page having to poll a database for a change

I have a ASP .NET web service that leverages a long lived connection from the client.

During the 15 minutes, the Web Service checks for a change in a field value in a record in an SQL table. If that value changes it then immediately sends a response to the client with ReadMessage. This checking / polling of the database is done every 30 seconds. This has several drawbacks:

  1. it does not scale well. It works well with 1 or 2 clients, but when you end up with 10,000 client connection that is a lot of polling on the database.
  2. It leads to latency in processing as it may take up to 30 seconds for the client to be notified.

What I would like is to find a way of notifying the Web Service for the active http client that the record has been updated.

It should also be noted that each client connection to the web service has it's own specific record in the table.

Upvotes: 3

Views: 333

Answers (3)

Magnus
Magnus

Reputation: 46929

I think SqlDependency is what you are looking for. Query Notifications allow applications to receive a notice when the results of a query have been changed

Upvotes: 2

Brandon Moore
Brandon Moore

Reputation: 8780

You could put a trigger on the table. Disclaimer: I try to stay away from triggers because it's very easy to write one poorly and when it errors it's hard to debug. However, I haven't ever written a CLR trigger and I imagine there's a little more safety in that since you have more control over error handling.

But even better would be to have whatever process is updating the table to begin with notify your webservice of the change if that's an option.

Upvotes: 0

Rondel
Rondel

Reputation: 4951

Have you considered setting up some triggers in the db? If you are using SQL Server you can use SQL Server CLR integration.

http://msdn.microsoft.com/en-us/library/ms254963%28v=VS.80%29.aspx

Upvotes: 2

Related Questions