PDA

View Full Version : Report Optimizing.



Michael Mullan
24-May-2010, 04:33 PM
I have a report which uses 4 tables, as per the attached screenshot.

Is there yet any way to optimize the finding of records for this report?

In the left most file, with only 5 records, I want all of them, and as I move left to right, the constraints narrow the selections greatly. In the final count there are only about 600 records used for the entire report.

I use this loop, which spins through basically all of the data, to pre-calculate the patient's balance.

For_All Patient by 10
Constrain PATIENT.INS_FLAG eq "I"
Constrain PATIENT.PATIENT_ID gt "^ZZZZZZZ"

do // all Customers by Name
For_All SCHED by 4 // all scheduled appts for patient
Constrain SCHED relates to Patient
Constrain sched.Date Between DateLo and dateHi
Constrain sched as (pos(SCHED.STAFF_DISP, "|OK|MH|MF") GT 0)
do
Lock
Clear BILL
Move SCHED.PATIENT_ID to BILL.PATIENT_ID
Find EQ BILL by Index.1
[FINDERR] Begin
Move PATIENT.PAT_BAL to BILL.BAL
Move PATIENT.INS_FLAG to BILL.INS_FLAG
End
Move (BILL.BAL+SCHED.PAID-(SCHED.PAT_FEE+SCHED.INS_FEE)) to BILL.BAL
Move (BILL.NRECS+1) to BILL.NRECS
SaveRecord BILL
Unlock
End_For_All

End_For_All
This pre-load of the temp table takes only 2 seconds to run, but the report takes several minutes.

How do I go about optimizing this?