SqlDependency performance
I have a web application that use a SQL Server database that is shared with others web applications (over which I have no control). I must know when any of the web apps makes changes to some tables in the database.
My first idea was to use SqlDependency (specifically SqlTableDependency, because I need to know the changed data), but I'm concerned about performance problems.
I want to know if there is any performance comparison over SqlDependency (not SqlTableDependency), triggers (that fires WS, exe, etc.), and polling.
I found some questions and articles, but they are not clear enough to me
- Using SQLDependency vs. periodic polling of a table (performance impact)
- .NET SqlDependency with many notifications vs. high rate polling?
- How efficient SqlDependency (Service Broker) is compared to query by timer?
- SqlDependency vs constant polling of a SQL Server database
- Design consideration for data retrieval : pooling vs SqlDependency and SqlCacheDependency
- Query Notification & Service Broker
Other info:
- Five tables to monitor
- About 1 change per second on each table
- Tables grow about 100 rows per day
Thank you!
Answer
The short answer is...there may be too many moving pieces to directly compare performance prior to implementation. I will speak on performance but also at play here is determining which options satisfy the basic demands of our application.
SqlDependency vs. SqlTableDependency and Triggers
Though SqlDependency and SqlTableDependency are similar in name, the behavior and results of implementing either are so very different. Unlike SqlDependency, SqlTableDependency supplements the Queue with other database objects, namely a Trigger on the targeted table.
In my application, which handles SignalR messaging from server to client, SqlDependency is a non-starter because on its own it does not give me the real-time data I need to read from the db changes. In all fairness, it was not designed this way. Any data would be procured from a seperate db call. So any efficiencies or perceived speed are practically nullified. If data is changing frequently enough, and without enough workarounds, calling a procedure immediately after a change notification may not even bring back the data we are looking for.
Brick walls such as these led me to implement SqlTableDependency. What may not be instantly clear from the documentation is that SqlTableDependency automatically populates database objects on Start(). So we do not have to spend time authoring Sql procs. The main disclaimer here is that not only does your DBA have to be onboard with setting the DB broker enabled, but also permissions to create and remove triggers. One must be careful to Stop() a SqlTableDependency or the triggers will have to be manually cleared from the db. If you want to guarantee poor performance, leave dozens of triggers on a table! Not only will your notifications take longer, but anyone querying the table for data will experience slowdowns.
That being said, we only need one SqlTableDependency trigger to send messages to thousands of clients or send the data to as many services as necessary. From that perspective, I would qualify it as efficient.
Here is a brief mention on performance from the SqlTableDependency documentation:
Load Test and Performance SqlTableDependency support notifications triggered by DML executed every 50 milliseconds. Test realized with two client applications concurrently executing 10.000 random insert/update/operation on same table.(https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency)
Polling
One could set up a service to poll each of the 5 tables to monitor. However, depending on whether we need to handle the data within each change or whether we simply need to know that something changed, I would estimate handling the data from polling would get cumbersome with such a severe frequency (by your estimate 1 per second).
Recommendation
Create as many varities of implementations as time allows and observe the behavior of each. Some will rule themselves out prior to any load testing. SqlTableDependency is a solid place to begin.
With each approach comes a unique implementation, and thus performance barometers must address how the supporting code and data environments factor into the overall picture.
sqldependencysignalrsqltabledependencytriggerspollingsqlperformance