March 5, 2017 at 6:15 pm
Looking for input on a desired result concerning multiple database and having a process "poll" one DB to copy data to another..
The business request/workflow is..
Call center wants agents/users to be visually notified when a blacklisted phone # exists in either one of two Phone# fields on Database A but only when its an "active" call.. (a record on Database A can be either 'active' or 'inactive' as determined by a flag on a table in Database A).
The master blacklist of phone #'s exists on a separate database (Database B) in its own table. I want to minimize demand on Database A from the 2nd aspect of the business request so I was thinking of having a stored procedure job on a non-critical db (Database B) to "poll" database A every 30 seconds.. and it copies relevant (active) rows from DB A to DB B to an 'activecalls' table. the 'activecalls' table would then be the one queried by a hypothetical 3rd party app that runs on client machines and checks for phone #'s in blacklist that are presently existent in the 'activecalls' table..
Seeking input for the most efficient way to get activecalls table populated from DB A on a 30-second polling cycle.. should i truncate 'activecalls' table every 30 seconds with a new set of data? there is a primary key on DB A table so maybe activecalls table should only update if there is 'modified' data in an already existent record.
Thanks in advance! - please let me know if further explanation is necessary.
March 6, 2017 at 3:34 am
I try to fall to moving as little data as possible, so if you have a mechanism of tracking create/update date, then use that as part of your polling process and only move the data that you need.
Is the list of phone numbers updated that often? If not, you might want to just move that into the initial call tracking database and do a pretty straightforward seek on the number in near real time, even joining to it as part of the initial write to the call track table (or whatever).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2017 at 8:35 am
I'm with Grant. I'd prefer you actually don't move the data.
If you had to, why another db? You could use a trigger to check for new numbers being part of a blacklist, though I try to avoid triggers. If the format is known, then just index on phone number, both the original and blacklist tables, and join them for a list of issues.
What I'm not clear about is how you do the notifications? If I perform data entry of a phone, and it's blacklisted, does the data entry app let me know? Or are you enabling some third party app to poll the table? The latter becomes less scalable as you grow data entry, and could be a point of contention. Knowing more about the actual process might be helpful.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply