I have a project where a part of it monitors changes made to an SQL database. I am using the SQL Table Dependency NuGet Package to monitor changes so I can update the UI when changes are made.
The issue I have is that there is a function in my program that can add 50-99k rows to a table in the database. The event gets triggered as many times as there are rows added. This is problematic because I do not want to update the UI 99k times. I want to update it at most once or twice. How I am handling right now is when I detect that 5 events are triggered within a certain timespan I DeInit the TableDependency, then a delayed task reenables it after a few seconds and also triggers a UI update at the end so it won't miss anything while it was disabled temporarily.
I also tried using a static bool for rate limiting, instead of DeIniting and ReIniting the TableDependency, but it takes 30-90s sometimes because the event handler cannot reasonably keep up with them all. I think the DeInit works better because removing the callbacks from the eventhandler appears to clear it from events. I could not find a way to clear the event handler from the massive queue of events otherwise.
I tried delving into Reactive Extensions and using the Throttle function. This worked OK except for the fact that the first event received would not trigger. It would wait until the events died off to trigger (I realize this is by design). This makes the program feel unresponsive for awhile because when the events are triggered SQL has already added all the rows so all it really needs is that first event and last event to update at most.
The reason I am trying to find an alternative is because TableDependency sometimes (no idea how to replicate this yet) is orphaning Trigger scripts in SQL on the table with invalid ids and it causes fatal exceptions to occur when the DB Instance (I am using EF6 Core) runs SaveChanges(). I theorize running the DeInit and Init functions frequently is at best not helping the issue and at worst the direct cause of it. So I am trying to find some way to avoid frequently DeIniting and ReIniting the TableDependency but also have my UI updates feel responsive and not have bad performance.
DeInit function:
private static void DeInitDependency(TableType tableType)
{
if(tableType == TableType.Event)
{
eventTableDependency.Stop();
eventTableDependency.Dispose();
eventTableDependency.OnChanged -= SqlDependencyEventTable_OnChanged;
eventTableDependency.OnError -= DepEvent_OnError;
eventTableDependency.OnStatusChanged -= DepEvent_OnStatusChanged;
eventChangeTrackingStarted = false;
}
else if (tableType == TableType.Location)
{
locationTableDependency.Stop();
locationTableDependency.Dispose();
locationTableDependency.OnChanged -= SqlDependencyLocationTable_OnChanged;
locationTableDependency.OnError -= DepLocation_OnError;
locationTableDependency.OnStatusChanged -= DepLocation_OnStatusChanged;
locationChangeTrackingStarted = false;
}
}
Init/Reinit Function:
public static void InitDependency(TableType tableType)
{
try
{
//Set Connection String to SQL
string dbConnectionString = "";
dbConnectionString = sqlCore.generateConnectionString();
if(tableType == TableType.Event)
{
//Create Dependency and Connect
eventTableDependency = new SqlTableDependency<NextGenGui.Models.Event>(dbConnectionString, executeUserPermissionCheck: false);
eventTableDependency.OnChanged = SqlDependencyEventTable_OnChanged;
eventTableDependency.OnError = DepEvent_OnError;
eventTableDependency.OnStatusChanged = DepEvent_OnStatusChanged;
eventTableDependency.Start();
eventChangeTrackingStarted = true;
Debug.WriteLine("Event SQL TRACKING STARTED!");
}
else if(tableType == TableType.Location)
{
locationTableDependency = new SqlTableDependency<Models.Location>(dbConnectionString, executeUserPermissionCheck: false);
locationTableDependency.OnChanged = SqlDependencyLocationTable_OnChanged;
locationTableDependency.OnError = DepLocation_OnError;
locationTableDependency.OnStatusChanged = DepLocation_OnStatusChanged;
locationTableDependency.Start();
locationChangeTrackingStarted = true;
Debug.WriteLine("Location SQL TRACKING STARTED!");
}
}catch (Exception ex)
{
Debug.WriteLine(ex);
if(ex.Message.Contains("Service broker"))
{
InitSQLBrokerSetting();
}
}
}
CodePudding user response：
It sounds like you need one event per business-level operation, instead of one event per table update. If that's the case, then you're going to have to look at implementing your own solution. Topics like SignalR and ServiceBus are good starting points to look into. This stream of business operations is a useful thing to implement anyway, for auditing and caching.
It's worth pointing out that you don't have to completely replace the SQL Table Dependency in one go. You can start with just the tables that are causing problems from the bulk operations.
CodePudding user response：
You effectively need to debounce the event signaling. Rather that removing the event handler (which means you won't know what rows have changed during that period) could your handler be changed to simply make marks in memory state based on what the current UI might be caring about? For instance if the UI is displaying one or more key records that you care about, the handler knows what IDs are relevant and if any of those rows are touched the markers are set, in which a periodic check looks at the markers and refreshes the view. This might be a single row the user is viewing, or a set of row IDs based on something like search results, etc.
If instead the UI reflects a summary of all data state and any row change would impact it, then perhaps consider an in-memory representation that can be updated by the event handler and periodically checked and refresh the view if necessary.
Ultimately it depends on what the view is currently displaying and the relationship with regards to the data update monitoring. Libraries like SignalR are typically employed for more server operation to relevant clients signaling where actions invoked by one client can be relayed to other clients to update data or refresh their view. When it comes to something from the database you would probably want to implement some manner of filtering and processing to monitor when relevant changes have come in to raise a signal for a stale view check to pick up on and refresh.