SLOW processing report

[GCCkjn]

Hi
I'm using Crystal version 11. I have a rather complicated report (for me at least) which is providing the results I wanted but the running time is EXTREMELY slow and while it is running is also slows down our database system for all other users.

Would it be possible for someone to look at the actual report and tell me what I have done wrong and how I can correct it???

 

[Turkbear]

Hi,
I usually will not download files from folks I do not know.

What database are you using ?
Can you post just the SQL created by the report?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[GCCkjn]

Hi turkbear - We are using WinCIS, a database developed by Momentum Healthware (developed for health care providers). The SQL statement follows:


SELECT "Pat"."CPINo", "Activity"."ActivityDateTime", "ServCat"."AccountNo", "ActivityType"."ActivityTypeDesc", "Pat"."FirstName", "Pat"."LastName", "Person"."FirstName", "Person"."LastName", "WorkloadType"."WorkloadTypeDesc", "Activity"."ActivityNo", "Pat"."BirthDate", "ServCat"."ServCatDesc", "Person"."Specialty", "County"."Name", "PatAddr"."EndDate", "PatAddr"."StartDate", "ActivityGeneric"."ActivityGenericDesc"
FROM {oj ((((("HTI"."Activity" "Activity" INNER JOIN ((((("HTI"."PatAddr" "PatAddr" INNER JOIN "HTI"."Pat" "Pat" ON "PatAddr"."MasterNo"="Pat"."MasterNo") INNER JOIN "HTI"."Addr" "Addr" ON "PatAddr"."AddrID"="Addr"."ID") INNER JOIN "HTI"."ActivityResource" "ActivityResource" ON "Pat"."ResourceID"="ActivityResource"."ResourceID") INNER JOIN "HTI"."City" "City" ON "Addr"."CityID"="City"."ID") INNER JOIN "HTI"."County" "County" ON "City"."CountyID"="County"."ID") ON "Activity"."ActivityNo"="ActivityResource"."ActivityNo") LEFT OUTER JOIN "HTI"."ActivityGeneric" "ActivityGeneric" ON "Activity"."ActivityGenericID"="ActivityGeneric"."ActivityGenericID") INNER JOIN (("HTI"."ActivityTypeWorkloadType" "ActivityTypeWorkloadType" INNER JOIN "HTI"."WorkloadType" "WorkloadType" ON "ActivityTypeWorkloadType"."WorkloadTypeID"="WorkloadType"."WorkloadTypeID") INNER JOIN "HTI"."ActivityType" "ActivityType" ON "ActivityTypeWorkloadType"."ActivityTypeNo"="ActivityType"."ActivityTypeNo") ON "Activity"."ActivityTypeNo"="ActivityType"."ActivityTypeNo") INNER JOIN "HTI"."ActivityResource" "ActivityResource_1" ON "Activity"."ActivityNo"="ActivityResource_1"."ActivityNo") INNER JOIN "HTI"."ServCat" "ServCat" ON "Activity"."ServCatID"="ServCat"."ServCatID") INNER JOIN "HTI"."Person" "Person" ON "ActivityResource_1"."ResourceID"="Person"."ResourceID"}
WHERE (("Activity"."ActivityDateTime">={ts '2009-12-24 11:44:59'} AND "Activity"."ActivityDateTime"<{ts '2009-12-24 11:46:00'}) AND NOT ("County"."Name"='Halton' OR "County"."Name"='Peel' OR "County"."Name"='Simcoe' OR "County"."Name"='Toronto Metro' OR "County"."Name"='York') AND "ServCat"."AccountNo" LIKE '7%46020' AND ("WorkloadType"."WorkloadTypeDesc"='Client Related - Direct' OR "WorkloadType"."WorkloadTypeDesc"='Work') AND NOT ("ActivityType"."ActivityTypeDesc"='DPSLP Program Orientation' OR "ActivityType"."ActivityTypeDesc"='DPSLP-Clin.Coord-famTELcontact' OR "ActivityType"."ActivityTypeDesc"='GCC-Clin.Coord-FamTEL contact' OR "ActivityType"."ActivityTypeDesc"='Initial Assessment-Multi Disc' OR "ActivityType"."ActivityTypeDesc"='Initial Assessment-Single Disc' OR "ActivityType"."ActivityTypeDesc"='Initial*' OR "ActivityType"."ActivityTypeDesc"='SLP Orientation Playgroup') AND NOT ("ServCat"."ServCatDesc"='IHP-AVT' OR "ServCat"."ServCatDesc"='SLP-Infant Hearing Pgm') OR "ActivityType"."ActivityTypeDesc"='Report Writing/Charting' AND "ActivityGeneric"."ActivityGenericDesc" LIKE 'Finish%' AND ("PatAddr"."EndDate" IS NULL OR "PatAddr"."EndDate">={ts '2010-01-09 00:00:00'}) AND "PatAddr"."StartDate"<{ts '2010-01-09 00:00:00'})
ORDER BY "Pat"."CPINo"


There is also a subreport with the following SQL query, linked by pat.cpino and activity.activityno:


SELECT "ActivityGeneric"."ActivityGenericDesc", "Pat"."CPINo", "Activity"."ActivityDateTime", "Activity"."ActivityNo"
FROM (("HTI"."Pat" "Pat" INNER JOIN "HTI"."ActivityResource" "ActivityResource" ON "Pat"."ResourceID"="ActivityResource"."ResourceID") INNER JOIN "HTI"."Activity" "Activity" ON "ActivityResource"."ActivityNo"="Activity"."ActivityNo") INNER JOIN "HTI"."ActivityGeneric" "ActivityGeneric" ON "Activity"."ActivityGenericID"="ActivityGeneric"."ActivityGenericID"

 

[Turkbear]

A couple of things I noticed right off ( not sure if they are the issue, but):

Any NOT clause in a where statement can cause a slowdown ( especially when used with OR connectors) since every record needs to be checked to see if it meets the criteria.

Where is the subreport inserted? Placement can be an issue if it needs to run for every new record returned in the main report.

Have you run the query, if possible, directly against that database to see how fast it returns data?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[GCCkjn]

You’re probably right about the ‘not’ and ‘or’ statements causing the report to run slowly but I don’t know of any other way to get the info I need. And I’ve just found out I now have to add another ‘not’ statement to the report. As well, the subreport is in the detail section of the report which is probably not good either (sigh).

So, let’s say I’m not able to get the report to run any faster. Is there a way for me to automate this report to run every evening when there are no users on the system?