Record Selection suggestions to speed up report?

[Former Member]

I have the following record selection:  This report runs very slowly.  Do you have any suggestions on how to improve my record selection that could speed the report up?

{PAT_ENC.CONTACT_DATE} = {?Date Range} and

{PAT_ENC.DEPARTMENT_ID} in [430094.00, 430096.00] and

({MEDICAL_HX.DX_ID} = 151849.00 or

{IP_FLWSHT_MEAS.FLO_MEAS_ID} = "3040130052" and {IP_FLWSHT_MEAS.MEAS_VALUE} >= '3' or

{IP_FLWSHT_MEAS.FLO_MEAS_ID} ="30401300342" and not(isnull({IP_FLWSHT_MEAS.MEAS_VALUE})) or

{IP_FLWSHT_MEAS.FLO_MEAS_ID} = "3040130047" and {IP_FLWSHT_MEAS.MEAS_VALUE} > '3')

and

{@pt age} <= 20

[abhilash_kumar]

Hi Bobby,

Go to the Database Option > Click 'Show SQL' Query.

Do you see each one of those filters being part of the where clause?

If yes, then the issue could be somewhere else - e.g: Subreports, Page N of M etc.

-Abhilash

[Former Member]

Yes, I see it.  SQL below.

SELECT DISTINCT "PATIENT"."PAT_NAME", "F_AN_RECORD_SUMMARY"."AN_START_DATETIME", "PAT_ENC"."CONTACT_DATE", "PAT_ENC"."DEPARTMENT_ID", "CLARITY_DEP"."DEPARTMENT_NAME", "PATIENT"."BIRTH_DATE", "V_FV_MRN"."MRN", "IP_FLWSHT_MEAS"."FLO_MEAS_ID", "IP_FLWSHT_MEAS"."MEAS_VALUE", "MEDICAL_HX"."DX_ID"
FROM   (((((("EPICADM"."PAT_ENC" "PAT_ENC" INNER JOIN "EPICADM"."PATIENT" "PATIENT" ON "PAT_ENC"."PAT_ID"="PATIENT"."PAT_ID") INNER JOIN "EPICADM"."F_AN_RECORD_SUMMARY" "F_AN_RECORD_SUMMARY" ON "PAT_ENC"."PAT_ENC_CSN_ID"="F_AN_RECORD_SUMMARY"."AN_52_ENC_CSN_ID") INNER JOIN "EPICADM"."CLARITY_DEP" "CLARITY_DEP" ON "PAT_ENC"."DEPARTMENT_ID"="CLARITY_DEP"."DEPARTMENT_ID") INNER JOIN "EPICADM"."IP_FLWSHT_REC" "IP_FLWSHT_REC" ON "PAT_ENC"."INPATIENT_DATA_ID"="IP_FLWSHT_REC"."INPATIENT_DATA_ID") INNER JOIN "EPICADM"."MEDICAL_HX" "MEDICAL_HX" ON "PATIENT"."PAT_ID"="MEDICAL_HX"."PAT_ID") INNER JOIN "EPICADM"."V_FV_MRN" "V_FV_MRN" ON "PATIENT"."PAT_ID"="V_FV_MRN"."PAT_ID") INNER JOIN "EPICADM"."IP_FLWSHT_MEAS" "IP_FLWSHT_MEAS" ON "IP_FLWSHT_REC"."FSD_ID"="IP_FLWSHT_MEAS"."FSD_ID"
WHERE  ("PAT_ENC"."CONTACT_DATE">={ts '2014-05-01 00:00:00'} AND "PAT_ENC"."CONTACT_DATE"<{ts '2014-06-01 00:00:00'}) AND ("PAT_ENC"."DEPARTMENT_ID"=430094 OR "PAT_ENC"."DEPARTMENT_ID"=430096) AND ("MEDICAL_HX"."DX_ID"=151849 OR "IP_FLWSHT_MEAS"."FLO_MEAS_ID"='3040130052' AND "IP_FLWSHT_MEAS"."MEAS_VALUE">='3' OR "IP_FLWSHT_MEAS"."FLO_MEAS_ID"='30401300342' AND "IP_FLWSHT_MEAS"."MEAS_VALUE" IS  NOT  NULL  OR "IP_FLWSHT_MEAS"."FLO_MEAS_ID"='3040130047' AND "IP_FLWSHT_MEAS"."MEAS_VALUE">'3')

[Former Member]

This report doesn't have a sub report.

[abhilash_kumar]

I don't see this being part of the where clause:

{@pt age} <= 20

Perhaps this is causing the lag? What does the formula do?

To see if removing the above filter has any effect on the query's runtime, try this:

Copy the SQL Query above > Add it using the 'Add Command' option > Refresh the report. How long does it take?

Also, how long does the same query take when run on the database client (e.g Toad, SQL Management Studio etc)?

-Abhilash

[DellSC]

I also see a potential issue with missing parentheses that might cause this to not filter your data correctly.  See the bold red parentheses below:

{PAT_ENC.CONTACT_DATE} = {?Date Range} and

(

  ({PAT_ENC.DEPARTMENT_ID} in [430094.00, 430096.00] and ({MEDICAL_HX.DX_ID} = 151849.00) or

  ({IP_FLWSHT_MEAS.FLO_MEAS_ID} = "3040130052" and {IP_FLWSHT_MEAS.MEAS_VALUE} >= '3') or

  ({IP_FLWSHT_MEAS.FLO_MEAS_ID} ="30401300342" and not(isnull({IP_FLWSHT_MEAS.MEAS_VALUE}))) or

  ({IP_FLWSHT_MEAS.FLO_MEAS_ID} = "3040130047" and {IP_FLWSHT_MEAS.MEAS_VALUE} > '3'))

)

and

{@pt age} <= 20

And to follow up on what Abhilash is trying to point out - it looks like your {@pt age} formula is can't be pushed down to the database for processing.  Instead, Crystal is pulling in ALL of the data that meets your other filter criteria and then filtering for age in memory.  If you have a lot data, this will definitely slow down your report.

There are two possible solutions:

1.  The best solution would be to create a stored function in your database that does the age calculation.  You would then create a "SQL Expression" that would call that function.  In your Filter, you would select the SQL Expression instead of the formula  So, it might look like this:

{%pt age} <= 20

2.  Write the functionality to calculate the age in a SQL Expression - this would use the syntax from your database, NOT Crystal syntax!

-Dell