Report Processing Time

[RepRider]

CRXI and SQL

My question is about being able to shorten report processing time. I have a report that has 7 groupings and displays a current case load per provider. It is 63 pages long and sorts by "team" and "provider." It has 10 coumns of information pertaining to each person listed on the providers case load many showing the latest date of a particular service.

The full 63 page report takes about 4 hours to run.
When I filter for one of the four teams it takes about an hour. If we filter for an individual provider it takes about 10 minutes.

I have noticed that the time the report is accessing the database is actually much shorter than the time it takes "recalucating summaries."

I went into the design and took out any summaries we weren't actually using. They were hidden in footers. However, this did not help much.

Does anyone have any advice on how I can shorten the processing time? I don't know what information you might further need but I would be happy to provide it.

The report selection formula for one of the teams would be this:

not ({Episode_Open_Case_View_dmc.consumer_ID} in [20227, 20228, 20229, 20345, 20357, 20435]) and
not ({Employee_View.people_last_name} in ["", "BEHAVIOR MANAGEMENT", "CONTRACTED", "FRITSCHY"]) and
{Employee_View.supervisor__employee_ID} = 43

The full report would just have more numbers in the last line.

 

[IanWaterman]

Its more likely to be your views.

Make sure some one has not helpfully added

Order By ....

At the end of each view, if they have then remove them, Order By slows the execution of a view considerably.

Ian

 

[RepRider]

Thanks, I am not sure where to look as a semi-novice.
Here is the SQL query...

SELECT DISTINCT "Employee_View"."people_last_name",
"Episode_Open_Case_View_dmc"."consumer_ID",
"Consumer_View"."people_last_name", "Consumer_View"."people_first_name",
"PeriodicSummary_View"."periodicsummary_ID",
"PeriodicSummary_View"."assessment_date",
"Psychosocial_View"."psychosocial_ID",
"Psychosocial_View"."assessment_date",
"Consumer_Service_View"."begin_date", "Employee_View"."people_first_name",
"Consumer_View"."people_phone_number",
"Episode_Open_Case_View_dmc"."begin_date",
"Employee_View"."supervisor__employee_ID",
"Consumer_View"."ConsumerPaymentSource_CD_OL",
"Cafas_view_dmc"."begin_date", "Consumer_Income"."begin_date"
FROM (((((("SC_Production"."dbo"."Episode_Open Case_View_dmc"
"Episode_Open_Case_View_dmc" LEFT OUTER JOIN
"SC_Production"."dbo"."Consumer_View" "Consumer_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Consumer_View"."consumer_ID")
LEFT OUTER JOIN "SC_Production"."dbo"."PeriodicSummary_View"
"PeriodicSummary_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="PeriodicSummary_View".
"consumer_ID") LEFT OUTER JOIN "SC_Production"."dbo"."Psychosocial_View"
"Psychosocial_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Psychosocial_View".
"consumer_ID") LEFT OUTER JOIN "SC_Production"."dbo"."Employee_View"
"Employee_View" ON
"Episode_Open_Case_View_dmc"."primary_service__provider_ID"=
"Employee_View"."employee_number") LEFT OUTER JOIN
"SC_Production"."dbo"."Consumer_Service_View" "Consumer_Service_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Consumer_Service_View".
"consumer_ID") LEFT OUTER JOIN "SC_Production"."dbo"."Cafas_view_dmc"
"Cafas_view_dmc" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Cafas_view_dmc"."consumer_ID")
INNER JOIN "SC_Production"."dbo"."Consumer_Income" "Consumer_Income" ON
"Consumer_View"."consumer_ID"="Consumer_Income"."consumer_ID"
WHERE NOT ("Episode_Open_Case_View_dmc"."consumer_ID"=20227 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20228 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20229 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20345 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20357 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20435) AND NOT
("Employee_View"."people_last_name"=' OR
"Employee_View"."people_last_name"='BEHAVIOR MANAGEMENT' OR
"Employee_View"."people_last_name"='CONTRACTED' OR
"Employee_View"."people_last_name"='FRITSCHY') AND
"Employee_View"."supervisor__employee_ID"=43
ORDER BY "Employee_View"."supervisor__employee_ID",
"Employee_View"."people_last_name", "Consumer_View"."people_last_name",
"Consumer_View"."people_first_name" DESC,
"Psychosocial_View"."psychosocial_ID" DESC,
"PeriodicSummary_View"."periodicsummary_ID" DESC

How do I remove the order by..
won't this change the report layout?

Thanks in advance

 

[IanWaterman]

This is the Crystal Query, I mean the design of the views used within this query.

"SC_Production"."dbo"."Episode_Open Case_View_dmc"
"SC_Production"."dbo"."Consumer_View"
"SC_Production"."dbo"."PeriodicSummary_View"
"SC_Production"."dbo"."Psychosocial_View"
"SC_Production"."dbo"."Employee_View"
"SC_Production"."dbo"."Consumer_Service_View"
"SC_Production"."dbo"."Cafas_view_dmc"
"SC_Production"."dbo"."Consumer_Income"

YOu need to look at the design of these and make sure there are no order by clauses within them.

Ian

 

[lbass]

You appear to have an inner join on a table that is left outer joined to an earlier table, and I think that should be left outer joined, also.

And you are adding selection criteria to a left outer joined table in the where clause, which will effectively undo the left join (unless you move it to the from clause, as shown below).

Also note that using "select distinct" will slow a report.

You might try setting up this report as a command, where you change the query to:

SELECT DISTINCT "Employee_View"."people_last_name",
"Episode_Open_Case_View_dmc"."consumer_ID",
"Consumer_View"."people_last_name",
"Consumer_View"."people_first_name",
"PeriodicSummary_View"."periodicsummary_ID",
"PeriodicSummary_View"."assessment_date",
"Psychosocial_View"."psychosocial_ID",
"Psychosocial_View"."assessment_date",
"Consumer_Service_View"."begin_date",
"Employee_View"."people_first_name",
"Consumer_View"."people_phone_number",
"Episode_Open_Case_View_dmc"."begin_date",
"Employee_View"."supervisor__employee_ID",
"Consumer_View"."ConsumerPaymentSource_CD_OL",
"Cafas_view_dmc"."begin_date",
"Consumer_Income"."begin_date"
FROM (((((("SC_Production"."dbo"."Episode_Open Case_View_dmc"
"Episode_Open_Case_View_dmc"

LEFT OUTER JOIN
"SC_Production"."dbo"."Consumer_View" "Consumer_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Consumer_View"."consumer_ID" and
NOT ("Episode_Open_Case_View_dmc"."consumer_ID"=20227 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20228 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20229 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20345 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20357 OR
"Episode_Open_Case_View_dmc"."consumer_ID"=20435))

LEFT OUTER JOIN "SC_Production"."dbo"."PeriodicSummary_View"
"PeriodicSummary_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="PeriodicSummary_View".
"consumer_ID")

LEFT OUTER JOIN "SC_Production"."dbo"."Psychosocial_View"
"Psychosocial_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Psychosocial_View".
"consumer_ID")

LEFT OUTER JOIN "SC_Production"."dbo"."Employee_View"
"Employee_View" ON
"Episode_Open_Case_View_dmc"."primary_service__provider_ID"=
"Employee_View"."employee_number"
AND NOT
("Employee_View"."people_last_name"=' OR
"Employee_View"."people_last_name"='BEHAVIOR MANAGEMENT' OR
"Employee_View"."people_last_name"='CONTRACTED' OR
"Employee_View"."people_last_name"='FRITSCHY') AND
"Employee_View"."supervisor__employee_ID"=43
)

LEFT OUTER JOIN
"SC_Production"."dbo"."Consumer_Service_View" "Consumer_Service_View" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Consumer_Service_View".
"consumer_ID")

LEFT OUTER JOIN "SC_Production"."dbo"."Cafas_view_dmc"
"Cafas_view_dmc" ON
"Episode_Open_Case_View_dmc"."consumer_ID"="Cafas_view_dmc"."consumer_ID")

LEFT OUTER JOIN "SC_Production"."dbo"."Consumer_Income" "Consumer_Income" ON
"Consumer_View"."consumer_ID"="Consumer_Income"."consumer_ID"

ORDER BY "Employee_View"."supervisor__employee_ID",
"Employee_View"."people_last_name", "Consumer_View"."people_last_name",
"Consumer_View"."people_first_name" DESC,
"Psychosocial_View"."psychosocial_ID" DESC,
"PeriodicSummary_View"."periodicsummary_ID" DESC

You could also try removing the "distinct" and see what impact that has on performance.

-LB

 

[RepRider]

Thank you so very much!

I will be trying these suggestions and will let you know how it works out.

 

[RepRider]

I have saved all the suggestions and will be trying them one by one. I have been thinking about the overall design (I wasn't the author) and wondered if all the grouping is needed. The desired output will look something like this:

Unit
Provider
Consumer|first service|last srvc|last assm’nt|last testing
(L,Fname) (date) (date) (date) (date)

The groupings will be by Unit, then by Provider with a list of consumers and the relative data across the row.

Could the groups be limited to Unit, Provider, and consumer with the related consumer data that calls for the last date of something be a MAX summary for that data field.

All of the above would be in one group header.

It's at the end of the day and maybe my brain is fried but could you tell if I am way off base in my thinking or not? I am desparately trying to streamline this report to be run as efficient as possible. It's taking 4 plus hours for 4 units, 35 providers and about 900 consumers.

lbass I did change the inner join you noticed to an outer join. I am having problems testing as I can't do this during the day as it slows down the system. I have to wait until after hours when I should be home.

 

[lbass]

The following might be helpful:
thread149-1276307

-LB

 

[Turkbear]

Hi,
Also, when using a NOT operator, it is vital to have the fields being searched indexed - to determine a NOT, every record must be checked.





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