Using SqlDependency vs. periodic polling of a table (performance impact)

Question

In the beginning of our app's development, we were using SqlDependency quite heavily to cache DB results until the notifications told our app to grab a fresh copy.

During testing, we've noticed that the SQL DB's performance was getting hammered by the SqlDependency notification service. We scaled back the number of tables that we were using SqlDependency and noticed a large gain in performance. So, we thought we were just over using it and we moved on. We are down to only a few tables now.

Later, we discovered that we couldn't scale back the security access level for the username that will establish the dependency. We could have more than one connection string for each DB (one for dependency and one for the rest of the app), but with multiple DBs and DB mirroring, this is a pain (from SQL DB admin point of view and app development).

At this point, we are just thinking about moving away from SqlDependency altogether based on the following logic:

1. We don't need "instant" notification that the data has changed. If we knew within 1 second, that would be fast enough.
2. With some slight refactoring, we could get it down to just 1 table and poll that table once a second.

Does anyone see a flaw in this logic?

Would polling one table once a second cause more or less load on the DB than SqlDependency?

Has anyone had similar performance issue with SqlDependency?

Answer 1

I do dare try answer your question. But I am not sure you'll get the answer you was hoping for...

I remember back in the early 90ies when Borland promoted this grand new feature of 'callbacks' in their database Interbase that would give the caller (Delphi) 'notifications' via some very nifty new tech where promises was made that the database could be 'active'.

This was later known as the 'waste of time theory'.

And I guess why this never took of is perhaps that while the concept of DBMS was looking very promising, the database is one of your tiers that you can only scale up and not horizontally.

So programming languages to the rescue. Or rather the idea of Service Oriented Architecture (SOA). Many confuse SOA for 'Webservices' that was indeed an included hype in this new concept.

But if you check out the Fiefdom/Emissary design pattern (or Master/Agent pattern renamed to make it sound more cool and professional), you will find that the major idea is having exclusive control of its resources (read databases) and that all calls are being funneled via one single data adapter.

Obviously such a design does not work at all with triggers nor any callback frameworks.

But I think you should reconsider your entire design. If you funnel all actions and all calls via a single 'DataLayer', perhaps using Entity Framework, and perhaps on top on that a Caching mechanism you would not have to rely on your database to forward messages back up the food chain.

To show how weird things can get when being to 'database-centric', here is an extreme actual live example of how not to send an email, written a long long time ago, by a coder I was not so much impressed with:

Fact 1: Sql Server can send emails.

Fact 2: Asp3 coder does not know if or how this can be done in VbScript.

Asp3: read textbox email-address, send to com+ layer

Com+: take email-address and forward to datalayer

Datalayer: take email-address and forward to a stored procedure

Sproc: take email-address and forward to sql function

function: do weird sub-string things to check that email-adress has @ . in it. return true or false.

Sproc: return a recordset with one column and one row containing 1 or 0

Datalayer: return the table as is.

Com+: convert the first column and row with value 1 or 0 to true or false

Asp3: if true, send email-adress with email subject and email text to com+

Com+: sends the exact information to datalayer

Datalayer: calls an stored procedure..

Sproc: calls a sql-function...

function: uses sql server email agent to send the email

If you read this far, my advice is to let sql server manage tables, relations, indexes and transactions. It is very good at that. Anything beyond those tasks, and with that I do include cursors in stored procedures, is better handled via proper code.