A multitennant database to be used by telsales agents, a table consists of the chance results in call. One record is displayed towards the agent at any given time for him to call, following the call the agent updates the record having a coded call outcome obtained from the end result table. Within this table some final results possess a recycle time, then the record ought to be displayed for an agent (any agent) to call again. Used 80% of calls can lead to the record being recycled such as this. There's also checking to limit the entire occasions known as, total length of time to call, and total calls in a single day. Agents may also specify to call an archive back themselves in a set time and date.

Draft Schema as one example of

Prospect table
PK
information

last call outcome
last call datetime
total occasions known as
occasions known as today
record_in_use_flag
(I am considering getting the front-end just update this table using the prospect info updates, call outcome and agent, a trigger updates the occasions known as fields/call date as well as updates the calls table.)

Calls Table
PK
FK to Prospect PK
call outcome
call datetime
agent known as
(Agents may update the chance record, these changes aren't monitored, but an eye on the phone call and result's saved)

Outcome Table
outcome code (PK)
recycle amount of time in minutes

Scheduled Callbacks Table
PK
FK to Prospect PK
datetime
agent

My question

What's the most performant method to choose the records that needs to be known as next which will scale the very best? Ive already made the decision to keep the final call particulars plus some calculated info (occasions known as etc) within the prospect table so a join towards the calls table is not required.

a)Should each database request records add the phone call outcome recycle time for you to the final call date after which filter for that TOP 1 (think this is far to slow)

b)Must i create another table with prospect PK, due call datetime for every record in prospect table that requires known as again. Could update this by:

1) another method that say went every ten minutes? Final results in which the recycle the years have transformed would want recalculated

2) within the trigger around the prospect table that runs once a result is up-to-date. Final results in which the recycle the years have transformed would want recalculated (This new table could be up-to-date when the recycle time to have an outcome transformed)

The issue I have faced using this type of situation is ensuring two operators don't want to result in the same call. Therefore the OUTPUT clause might help.

You can produce a function which calculates the following call time, after which make use of the OUTPUT clause to come back the phone call particulars towards the operator.

Something similar to:

UPDATE c SET NextCallTime = dbo.GetNextCallTime(CallID)
OUTPUT deleted.* --Which returns the copy of the row before the change is made
FROM 
(SELECT TOP 1 * FROM Calls ORDER BY NextCallTime) c
;

I tend not to endorse scalar functions, but it'll operate in this, because you are only ever calling it just one time. Put comments within the function to really make it very obvious it's not to be contacted a purchase BY clause or perhaps a WHERE clause, etc...

The advantage to presenting the OUTPUT clause is it returns that row that's being transformed, therefore it are only able to get to some single operator.