Scalable and performant way to run calculations against a database

Question

A multitennant database for use by telsales agents, a table contains the prospect leads to call. One record is displayed to the agent at a time for him to call, after the call the agent updates the record with a coded call outcome taken from the outcome table. In this table some outcomes have a recycle time, after which the record should be displayed to an agent (any agent) to call again. In practice 80% of calls will result in the record being recycled like this. There is also checking to limit the total times called, total number of days to call, and total calls in one day. Agents can also specify to call a record back themselves at a set date and time.

Draft Schema to Illustrate

Prospect table
PK
{contact details}
{XML fields to enable custom fields}
last call outcome
last call datetime
total times called
times called today
record_in_use_flag
(I'm thinking of having the front end just update this table with the prospect info updates, call outcome and agent, then a trigger updates the times called fields/call date and also updates the calls table.)

Calls Table
PK
FK to Prospect PK
call outcome
call datetime
agent called
(Agents may update the prospect record, these changes are not tracked, but a record of the call and outcome is stored)

Outcome Table
outcome code (PK)
recycle time in minutes

Scheduled Callbacks Table
PK
FK to Prospect PK
datetime
agent

My question

What is the most performant way to select the records that should be called next that will scale the best? Ive already decided to store the last call details and some calculated info (times called etc) in the prospect table so a join to the calls table is not necessary.

a)Should each database request for records add the call outcome recycle time to the last call date and then filter for the TOP 1 (think this would be far to slow)

b)Should i create another table with prospect PK, due call datetime for each record in prospect table that needs called again. Could update this by:

1) a separate procedure that say ran every 10 minutes? Outcomes where the recycle time has changed would need recalculated

2) in the trigger on the prospect table that runs once an outcome is updated. Outcomes where the recycle time has changed would need recalculated (This new table would be updated if the recycle time for an outcome changed)

Answer 1

The problem I've faced with this type of situation is making sure that two operators don't elect to make the same call. So the OUTPUT clause can help.

You could create a function which works out the next call time, and then use the OUTPUT clause to return the call details to the operator.

Something like:

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 don't like to endorse scalar functions, but it'll work in this scenario, because you're only ever calling it a single time. Put comments in the function to make it very clear that it's never to be called in an ORDER BY clause or a WHERE clause, etc...

The benefit to using the OUTPUT clause is that it returns that row that is being changed, so it can only be given to a single operator.