4

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?

3
  • 2
    See this SO post for some suggestions - Query Notification seems to be a better bet. But if you can implement a proxy between the external app and SQL Server, that'd be better.
    – gbjbaanb
    Jan 19 '16 at 9:30
  • @gbjbaanb Query Notification I guess is exactly what I have right now. Maybe the implementation I provided in the link above is poor in some ways? You're saying a proxy would be better. Do you have something in mind how to implement solution like that?
    – cah1r
    Jan 19 '16 at 12:16
3

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

1
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.

4
  • 1
    I'm curious, how would you scale your proposed solution to 500 client computers checking for updated records at the same time?
    – Dan
    Jun 12 '17 at 20:05
  • @Dan You remove the necessity to perform frequent checks. You don't fix conceptual problems with a technical fix in an ideal situation. You ultimately only delay the problem.
    – Neil
    Jun 13 '17 at 7:19
  • 1
    That's a really vague response that wasn't very helpful, but I guess so was my question so... shrugs. Good day to you.
    – Dan
    Jun 13 '17 at 23:46
  • @Dan You'll forgive me for not writing a second answer here in the comments section or modifying my year and a half old question to respond to your question. I only meant to say there is a reason for checking with such frequency. So you simply eliminate that reason, whatever it may be. I can't tell you what it is because OP didn't mention it apart from "saves" from an application.
    – Neil
    Jun 14 '17 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.