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
