SQL statement and report processing speed

[ehk]

Crystal 8.5
Advantage SQL

I’m designing a daily appointment schedule printout for medical office mgmt software. Primary table is APPT.DBF with left outer joins to PATIENT.DBF (for patient name and date of birth) and APPTCMTS (for appointment comments).

Parameters are Appointment Date, Location, Department, ID and Filter – all referencing fields that reside in the primary table.

The report has one Group based upon appointment time to separate AM from PM appointments.

Each time I print, it appears that Crystal is chugging through every record in the APPT.DBF before coming to the correct Date/Location/Department/ID combination, then displays the daily schedule. Have included the SQL statement in case it includes something that might shed light on how I can improve the report generation speed.
APPT.DBF is indexed on Appointment Date.

Thanks for the assistance...Ellen

SELECT
APPT."PAT_ID", APPT."DATE", APPT."TIME", APPT."LOCATION", APPT."DOCTOR", APPT."NAME", APPT."REASON", APPT."REMARKS", APPT."APPTSTAT", APPT."COMMENTS", APPT."DEPT",
APPTCMTS."NOTES",
PATIENT."BIRTH", PATIENT."BILL_ID"
FROM
{oj ("APPT" "APPT" LEFT OUTER JOIN "APPTCMTS" "APPTCMTS" ON ((("APPT"."LOCATION"="APPTCMTS"."LOCATION") AND ("APPT"."DEPT"="APPTCMTS"."DEPT")) AND ("APPT"."DOCTOR"="APPTCMTS"."DOCTOR")) AND ("APPT"."DATE"="APPTCMTS"."DATE")) LEFT OUTER JOIN "PATIENT" "PATIENT" ON "APPT"."PAT_ID"="PATIENT"."PAT_ID"}
WHERE
APPT."DATE" = '08/30/20'
ORDER BY
"APPT"."TIME" ASC

 

[lbass]

Please paste your record selection formula into the thread (report->selection formula->record). You mention that you have five parameters, but it appears that only one of them is making it to the SQL query (appt.date), so that suggests that your selection formula might not be optimized.

-LB

 

[ehk]

{APPT.DATE}={?DATE} and
UPPERCASE({APPT.LOCATION}) like {?LOCATION} and
UPPERCASE({APPT.DOCTOR}) like {?ID} AND
UPPERCASE({APPT.DEPT}) like{?DEPT} AND
NOT({APPT.APPTSTAT} IN{?FILTER})

 

[lbass]

You could replace each field that you are using Uppercase with by a SQL expression that makes the field uppercase. The syntax varies. In the Xtreme database (Access-based), you would use:

ucase(table.`string`)

...but see what is available in your function list for strings. After replacing the fields with the SQL expressions, those clauses should pass to the SQL.

I'm not sure why your last clause doesn't appear--what are the datatypes for {APPT.APPTSTAT} and {?Filter}?

-LB