Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I speed up run time for this particular report?

Status
Not open for further replies.

RepRider

Technical User
Jan 25, 2007
114
US
Using CR 8.5 and SQL database

We run a case list report at the beginning of the month that shows all open cases for each clinician. It involves linking 6 different views. It takes 45 minutes plus to run the report and I am thinking we could change something to reduce the run time, I just don't know what. We are fairly experienced with CR but certainly not experts. I am not sure what information to show so here is what I know. :)

The following is the formula viewable in the select expert:

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} in [161, 162, 168, 169, 170, 186, 43, 62]

The report displays the following data elements for the clinicians use:
Consumer Name, consumer ID, phone number, episode begin date, last service date, last assessment date, last periodic update date, and current insurance type.

The report has 7 groupings and is grouped by
1)Employee_View.supervisor_employee_ID,
2)Employee_view.people_last_name,
3)Consumer_view.people_last_name
and all of the above group headers are hidden (grayed in design view).
Group 4 is by consumer_view.people_first_name and is not suppressed but the data elements as described above are placed in this group header and display on the report.
Group 5 is psychosocial_view.psychosocial_ID,
Group 6 is PeriodicSummary_view.periodicsummary_ID,
Group 7 is Consumer_Service_view.begin_date

Group footers 7 thru 3 are hidden (grayed in design view).
Group footer 2 shows the total number of open cases for the clinician. Group footer 1 is grayed and empty.
The other footers have distinct counts and sums but are grayed in design view (hidden).
The details section is hidden.

The report footer displays the grand total of open cases.
The report is about 58 pages and the status line says records = 3863877.
There are 960 open consumers for about 47 clinians.

Should it take this long to run and what can we do to make this more efficient for processing? I didn't write it but my staff say its because of all the groups and having to find the last date for several of them.

Any help would be greatly appreciated!! Thank you in advance!
 
Ok, I just realized that I did not acutally have the SQL query displayed. If this is not too much information, here it is:
SELECT DISTINCT
Episode_Open_Case_View_dmc."consumer_ID", Episode_Open_Case_View_dmc."begin_date",
Consumer_View."people_first_name", Consumer_View."people_last_name", Consumer_View."people_phone_number", Consumer_View."ConsumerPaymentSource_CD_OL",
PeriodicSummary_View."periodicsummary_ID", PeriodicSummary_View."assessment_date",
Psychosocial_View."psychosocial_ID", Psychosocial_View."assessment_date",
Employee_View."people_first_name", Employee_View."people_last_name", Employee_View."supervisor__employee_ID",
Consumer_Service_View."begin_date"
FROM
{ oj (((("SC_Production"."dbo"."Episode_Open Case_View_dmc" Episode_Open_Case_View_dmc 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"."Consumer_View" Consumer_View ON
Episode_Open_Case_View_dmc."consumer_ID" = Consumer_View."consumer_ID"}
WHERE
(Employee_View."supervisor__employee_ID" = 62 OR
Employee_View."supervisor__employee_ID" = 43 OR
Employee_View."supervisor__employee_ID" = 186 OR
Employee_View."supervisor__employee_ID" = 170 OR
Employee_View."supervisor__employee_ID" = 162) AND
(Episode_Open_Case_View_dmc."consumer_ID" <> 20435 AND
Episode_Open_Case_View_dmc."consumer_ID" <> 20357 AND
Episode_Open_Case_View_dmc."consumer_ID" <> 20345 AND
Episode_Open_Case_View_dmc."consumer_ID" <> 20229 AND
Episode_Open_Case_View_dmc."consumer_ID" <> 20228 AND
Episode_Open_Case_View_dmc."consumer_ID" <> 20227) AND
(Employee_View."people_last_name" <> 'FRITSCHY' AND
Employee_View."people_last_name" <> 'CONTRACTED' AND
Employee_View."people_last_name" <> 'BEHAVIOR MANAGEMENT' AND
Employee_View."people_last_name" <> '')
ORDER BY
Employee_View."supervisor__employee_ID" ASC,
Employee_View."people_last_name" ASC,
Consumer_View."people_last_name" ASC,
Consumer_View."people_first_name" DESC,
Psychosocial_View."psychosocial_ID" DESC,
PeriodicSummary_View."periodicsummary_ID" DESC
 
It looks like everything is passing to the SQL statement. If you are not planning on using drilldown, you could suppress sections instead of hiding them.

Also, please see thread149-1276307 for other tips on speed.

-LB
 
Thank you, I will check the faq and suppress those sections, also take out any summmaries there that aren't being used. I will also check on the "perform grouping on server" and see if these make a difference.

Thanks checking it out!!
 
With nearly 4 million records for 58 pages of resulting data, I would suspect that you are pulling WAY too much data. Have you evaluated the detail lines to see if there are duplications or if there is some kind of Cartesian product in there? Also, I would be curious about the 6 views you are using. Is that the only way to retrieve the data? They each have to process each time you run the report so I would say 45 min is pretty good under those conditions. I'm just suspicious that, even though everything is being pushed to the server, your basic filter is too broad.
 
I have suppressed all the sections instead of hiding.
I have removed the fields in the suppressed details as we don't need to drill down.
I have removed some subtotals, totals in the suppressed footers.

Because the information comes from various tables/views created to get the necessary information related to both the consumer and the clinician I am told that all 6 views are necessary. I asked if new views could be created that would reduce the number of views and it was stated that because of the diversity of the data all current views were needed.

It appears that all that can be done is done and that 45 minutes is not that long due to the complexity... soooo.
Thanks for everyones look see!! I appreciate the many brains that are here for us to access! :)
 
RepRider -- assuming by SQL database you're talking MS-SQL, use the index tuning wizard, and execution plan analyzer to track down the bottle neck in your query performance (it will look through the views). If at all possible, avoid nested loop joins and table scan operations (go for index scans). If you have MS-SQL enterprise edition, make sure your views are indexed; if not, use HINTs in your query to force the query optimizer to use a certain index or indexes.

--MAL
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top