AnsweredAssumed Answered

Indexed Data vs Non-Indexed Data in finds.

Question asked by user28271 on Jul 31, 2017
Latest reply on Aug 1, 2017 by Daniel Farnan

Here is a very over-simplified example

 

TABLES:

 

WORKORDER db:  wo_id, bill_id

 

LABOR db: wo_id

 

BILLING db:  bill_id

 

LABOR to WORKORDER by wo_id

 

WORKORDER to BILLING by bill_id

 

FIND:

 

Find un-billed labor

 

OPTION 1:

 

Labor will have a text field "status" (defaulted to OPEN)

 

When you bill a WO a script will find all related labor and post BILLED to labor status field.

 

PRO: INDEXED, FAST

 

CONS: Slow to perform find. Opportunity for failure if a labor line is in use elsewhere.

 

 

OPTION 2:

 

Labor will have a calculated status field based on if the related WORKORDER has been billed.

 

PRO:  100% reliable, fast

 

CONS: Finds on an unindexed field can be very slow.  Overtime it become intolerably slow, like when you exceed 200K records.

 

 

 

What do you tend to do?

Is there a third option? 

Outcomes