Good C#.NET Solution to manage frequent database polling

Question

I am currently working on a c# .NET desktop application that will be communicating to a database over the internet via WCF and WCF Data Services. There will be many spots in the application that may need to be refreshed upon some interval. The easiest solution would be to just put these areas on a timer and requery the database. However, with thousands of clients connecting to the service layer and hence database, these operations would be very expensive to the server.

What I have considered is creating an RSS feed that is polled by the client, and lets the client know when these particular areas need to be updated. The RSS feed will be managed by a service that either polls the database for changes, or iterates through a list of items that are queued up by the WCF requests made by the client.

I have also considered creating some direct and continuous connection from the client to the server, but I am not sure what outbound firewall ports would be open from the client. I could probably only count on port 80/443.

So my question is what solutions have people had success implementing to solve this problem? Have people done RSS? Microsoft Sync Services? Two way communication between client and server over some save port via WCF?

Pharmacopoeia

Answer 1

I think you might want to go with a combination of two approaches. First off, you could use long polling from the client to server so that the server can notify the client as soon as a change occurs that the client is interested in.

A new technology that handles the above suggestion quite nicely in ASP.NET is SignalR. This handles much of the details of long polling (or uses WebSockets when it can) so you don't have to worry about it.

Secondly, based on the tags in this question it looks like you're using SQL Server. You could use database notifications on the tables you are interested in to have the DB notify your service when changes occur. This could then trigger the service to notify the client about the changes through the long poll connections. You can do this using the SqlDependency class.

I'm sure there are other ways, but this would probably scale quite well as you'd only have one service getting the notifications and then distributing them out to all the clients.

Heelandtoe

+1, but I worry about thousands of clients using long polling. Some environment optimization will probably be necessary for the open connections. I've never done this with WCF so I can't comment on the difficulties/positives. – Baritone

+1 Do you know of any good long polling implementations in C# using WCF? I've googled it and haven't come up with any good examples. – Pharmacopoeia

Sorry, I don't. I know there's a Duplex channel in WCF for Silverlight, but from what I've read it will poll periodically and not do long polling (msdn.microsoft.com/en-us/library/cc645028(VS.95).aspx) – Heelandtoe

Yeah. Saw the same thing. Thanks again. – Pharmacopoeia

Answer 2

you can define a callback interface in the WCF connection like this:

[ServiceContract(CallbackContract = typeof(IFooClient))]

As the client initiates the connection the should work through the firewall. The the server can method to register for changes an you can get the callback interface with

IFooClient client = OperationContext.Current.GetCallbackChannel<IFooClient>();

and callback all the clients that are registered on data changes.

Hydroxide

I like this approach as well. I will investigate it further. – Pharmacopoeia

Answer 3

If the expensive operation is the server to pooling the database, you should implement some sort of caching. It can be basic as ASP.NET caching or advanced as using memcached.

But if the expensive operation is the client pooling the server, you can use an Atom or RSS feed with PubSubHubbub to minimize the number of requests to the server. It'll get cheaper since there are some free PubSubHubbub publishers that will handle the load.

Reddin

Answer 4

You could look into the Service Broker, that way you won't have to poll for updates

Precedent

Answer 5

I don't know why RSS would be simpler than having your web services cache information from the database.

To compare the 2 options, let's say you need to know who edited an object in a report last (or the last time it was updated, etc.). With an RSS feed, you'll have to make a request for the feed, get it, parse it, and take action based on the relevant values. With a web service call that memoizes and caches it's database call, you just call the service and take action on the result. The only time I could see RSS being better is if 1) You are supporting multiple clients you either don't control or aren't .Net clients and 2) You are taking action based on a lot of the polled values at the same time.

Baritone