Faster By Multiple Search Or By Multiple Process

PointsWell

I have a process to handle the execution of a Contract.

The Contract has a number of Dates (12) these are Appointment BOs
Each Date has a number of Contacts to be notified (6)

I can either

set up a process to find all the dates and then pass each date to a series of processes to find each of the contacts and add them or
find all the dates and find one type of Contact and apply that to all the Dates then repeat the search and add the next type of Contact and repeat both searches (ie all the Dates and then the next type of Contact)

Neither of these seem particularly efficient, but one must be better than the other? Does anyone have an answer that isn't based on a hunch?

The first option feels like it touches too many records individually, the second object feels like it does too many searches for the same data.

BLOMASKY

If I am reading your question properly, 1 contract has 12 child records in the appointment table, if that is correct then I would have 2 processes,

P1 -> FIND Appointments where Appointment.ob_Contract = Contract
then call P2 with the Appointment BO as input
P2 -> Find the contacts and do whatever with them. (if necessary, have a P3 called for each Appointment
This way, you are not re-reading anything

Of course, would be easy - peasy to do in a SP.

Bruce

PointsWell

BLOMASKY wrote
If I am reading your question properly, 1 contract has 12 child records in the appointment table, if that is correct then I would have 2 processes,

P1 -> FIND Appointments where Appointment.ob_Contract = Contract
then call P2 with the Appointment BO as input
P2 -> Find the contacts and do whatever with them. (if necessary, have a P3 called for each Appointment
This way, you are not re-reading anything

Of course, would be easy - peasy to do in a SP.

Bruce

Yes in SQL it would be much faster, but I try to keep everything inside AIM where possible.

I suppose the question is what is the cost of searches versus the feeding of BOs individually.

PointsWell

BLOMASKY wrote
If I am reading your question properly, 1 contract has 12 child records in the appointment table, if that is correct then I would have 2 processes,

P1 -> FIND Appointments where Appointment.ob_Contract = Contract
then call P2 with the Appointment BO as input
P2 -> Find the contacts and do whatever with them. (if necessary, have a P3 called for each Appointment
This way, you are not re-reading anything

Of course, would be easy - peasy to do in a SP.

Bruce

I found a faster way using temporary BOs

P1 - Create a Temp BO with peer attributes for all of the different contact types
p2 - receives the Temp BO as input, find 1st Contact add it to BO.psContact1
p3 - received the Temp BO as imput, find 2nd Contact add it to BO.psContact2
p4-n - repeat for as many Contact records

Back to P1
FIND Appointments WHERE Condition
Appointment.psFaoContact1= Temp.psContact1 repeat for n Contact peers

Minimum number of FINDs and maximum number of records updated at a time.