Best way to get push notifications to server from MS SQL Server

Question

I partially found a solution to my question, but I'm not really satisfied with the result.

My application consists of ASP.NET MVC + MS SQL Server.

The case is as follows:

1. An external application saves data periodically to the SQL Database. In one tenth of a second, 20 to 100 records can be saved.
2. SQL should be polled from the back-end, or there should be push notifications after each save, in order to update (for example) a running total of saved items.
3. The back-end will push notifications to the UI (this will be done using SignalR)

I followed this tutorial http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/ to set it up.

SqlDependency with broker service is used to get push notifications from SQL firing the OnChange Event. The problem is that this solution is very slow. There is only one record saved every second for a small database, and the events fire with pretty big delays.

Is there another technology to get data changes from SQL? Or maybe the solution I'm using requires some kind of optimization?

Answer 1

Have a look at http://sqltabledependency.somee.com/. You can receive notification from SQL Server containing info about the inserted deleted or updated records.

Answer 2

You're trying to fix a conceptual problem with a technological one, in that you need to know when the database has been written to in order to update your program, and you're depending on this SqlDependency to achieve that. Don't get me wrong, SqlDependency is an excellent fit in some circumstances no doubt, but almost certainly in no such circumstance would it work well when mass inserts are being performed on the server.

Either the library has a feature that disables triggering the event after the first (or even better, after the last with some small delay), or you're going to have a solution that doesn't scale well.

I offer an alternative solution: When your backend task begins writing, when it finishes writing, and for every second while writing, it updates a record indicating that the database has been written to by that task.

Your application polls the database for that value every so often (say every 5 seconds), and if it has the indicator that it has been written to, you perform an update and you reset the flag. The next check performed happens only one second after the last in this case, and so in the case of database changes, it will update more frequently.

If you need to be more accurate with your checks, you may check more frequently as desired. What is important is that your program only performs checks and updates only as fast as you need, and not for every single record added. This means that when 100 records are added, you're not having to update your program data 100 times. If it indeed takes 0.1 seconds per 20-100 records, then you'll likely be updating 100 records at most 5 times.