Can I speed up this report?

[kate8]

Hi,
I have a report which displays 27 fields, group by person and show three most current value bases on three different dates. It takes 30 mines to run for only one month data. My user requests one year data. But for one month, the report has already went through 3 million records since the report has join 14 tables with 4 left join. When I try to run one year data, the report runs forever. I have to cancel the job. I have tried to use command, but still can’t get it run through.
The report could run complete (even it took long time) until join the last table with three fields displayed in the report. I couldn’t figure out why that last table cost that much time. I checked the forum and Internet to try to find the ways to speed up the report. But there is no luck. Currently, I don’t have permission to create store procedure in this database.
Is there any ideas or suggestions that could help the report run faster? Or any suggestions to explain why join the last table make the report run forever?
Thank you so much for any ideas!!!

 

[IanWaterman]

Are you sure most of the query is executing on DB. If you use formula in Select expert these will not always parse to DB and thus filter locally in report.

Please post your select statement

Ian

 

[IanWaterman]

Also last table being added is probably not indexed on the field you are linking to.

Can you add a filter to reduce data requested from that table.

Ian

 

[kate8]

Hi Ian,

Thank you so much for your respond!!
The basic idea for this report is to put out all the patients who were seen in year 2011 in depatment IM with visit type='Office Visit' and display all their latest check result, the main table is that "patient_encounter". I checked the Report Option "Perform Grouping on Server", but not sure what that could do since I group patient in the report lever, sort each result by their completed Date, then display the latest one on group header. They are done locally. I couldn't figure out that without creating store proceture, how I can do it in DB.
Here is the SQL Query from report, hope it is readable:

SELECT DISTINCT "person"."last_name", "person"."first_name", "person"."middle_name", "person"."date_of_birth", "person"."sex", "vital_signs_"."BMI_calc", "Social_Hx_"."tobacco_type", "master_im_"."dept", "patient_encounter"."enc_timestamp", "person"."person_id", "health_maint_"."lastcolonoscopy", "health_maint_"."lastmammogram", "health_maint_"."lastfecaloccltx3", "Social_Hx_"."tobacco_yes", "provider_mstr"."last_name", "provider_mstr"."first_name", "patient_encounter"."supervisor_provider_id", "provider_mstr_1"."delete_ind", "provider_mstr_1"."last_name", "provider_mstr_1"."first_name", "person_external_xref"."external_id", "patient_"."pcp_lastname", "patient_"."pcp_1st", "vital_signs_"."bp_systolic", "vital_signs_"."bp_diastolic", "vital_signs_"."create_timestamp", "patient_diagnosis"."icd9cm_code_id", "order_"."actTextDisplay", "order_"."completedDate", "patient_"."warning", "order_"."obsInterpretation", "master_im_"."visit_type"
FROM (((((((((("MedDB"."dbo"."patient_encounter" "patient_encounter" INNER JOIN "MedDB"."dbo"."master_im_" "master_im_" ON ("patient_encounter"."person_id"="master_im_"."person_id") AND ("patient_encounter"."enc_id"="master_im_"."enc_id")) INNER JOIN "MedDB"."dbo"."person" "person" ON "patient_encounter"."person_id"="person"."person_id") INNER JOIN "MedDB"."dbo"."provider_mstr" "provider_mstr" ON "patient_encounter"."rendering_provider_id"="provider_mstr"."provider_id") LEFT OUTER JOIN "MedDB"."dbo"."provider_mstr" "provider_mstr_1" ON "patient_encounter"."supervisor_provider_id"="provider_mstr_1"."provider_id") LEFT OUTER JOIN "MedDB"."dbo"."patient_diagnosis" "patient_diagnosis" ON "patient_encounter"."person_id"="patient_diagnosis"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."order_" "order_" ON "patient_encounter"."person_id"="order_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."Social_Hx_" "Social_Hx_" ON "master_im_"."person_id"="Social_Hx_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."health_maint_" "health_maint_" ON "master_im_"."person_id"="health_maint_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."vital_signs_" "vital_signs_" ON "master_im_"."person_id"="vital_signs_"."person_id") INNER JOIN "MedDB"."dbo"."patient_" "patient_" ON "person"."person_id"="patient_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."person_external_xref" "person_external_xref" ON "person"."person_id"="person_external_xref"."person_id"
WHERE ("patient_encounter"."enc_timestamp">={ts '2011-01-01 10:31:33'} AND "patient_encounter"."enc_timestamp"<{ts '2011-12-31 00:00:01'}) AND "master_im_"."visit_type"='Office Visit' AND
"master_im_"."dept"='IM'


Again, thanks a lot!

 

[IanWaterman]

Kate

Can't see anything wrong with query.

Are all the filters entered in select appearing in the query?

Failing that its probably an index issue on the last table added.

Ian

 

[lbass]

This thread might be helpful: thread149-1276307.

-LB

 

[kate8]

Ian and Lbass,

Thank you!!!
I have read this thread before I posted my question and tried all I can.

Ian, some filters that I have to use formulas, but only four of them. Because there are four result values are saving in the same field, they are identified by the test description. I can’t filter them in the query, can I? Maybe??
How to fix the index issue? The most join are using person_id, which is not PK, but a uniqueidentifier. The idea for report is simple, get all patients being seen in 2011, then display all the latest test. So I have to use group to show the latest. The data is not that big, it is less than 10,000 patients. But when I ran the report, I can see the report go through billions records. That must be the left join. If a patient had 10 test, then he would have 10 records in DB, the report must check all of them. Still I think Crystal report can handle it, but I can’t get it work.

 

[IanWaterman]

The formula you mention are not being parsed to DB and thus data set coming back to report is very large and thus takes a long time to process. You need to find a way to get them into the SQL

Please show contents of formula and how they are used in select statement.

You might not be able to get whole formula to parse, but if you can get elements then that might increase speed.

eg you might have

@formula
If field1 = x then field2 = y else field2 = z

In select statement you may have

@formula = y

This will not parse to SQL and all values of Field2 will be returned and filtered in Crystal.

as you only want field 2 to be x or y you can change select statement

@formula = y
and field2 in [z,y]


Ian

 

[IanWaterman]

The formula you mention are not being parsed to DB and thus data set coming back to report is very large and thus takes a long time to process. You need to find a way to get them into the SQL

Please show contents of formula and how they are used in select statement.

You might not be able to get whole formula to parse, but if you can get elements then that might increase speed.

eg you might have

@formula
If field1 = x then field2 = y else field2 = z

In select statement you may have

@formula = y

This will not parse to SQL and all values of Field2 will be returned and filtered in Crystal.

as you only want field 2 to be z or y you can change select statement

@formula = y
and field2 in [z,y]


Ian

 

[kate8]

Hi Ian,

I am not sure if my formulas should be called filters. No matter patients meet this condition or not, they should show in the report if they were seen in 2011 and department, visit type meet requirement. In the formular, if meet condition, then display values, otherwise that field will be blank. Here are two of them.
@formula1
if ({order_.actCode} like "123*" ) then
'YES'
else
"NO"

@formula2
if({order_.actCode} = "12345" and {order_.completed} = 1) then
{order_.result}

You mean I can add ( {order_.actCode} = "12345" or {order_.actCode} like "123*" )to the select statement?

 

[IanWaterman]

Sorry I thought you were using formula in select statement.

You can only filter on thoses fields if they are being used for conditional suppression of data.

eg if you suppressed a detail line where Formula1 = 'NO'

Ian

 

[kate8]

No, it was me. I said I use formula as filter. Actually they are not effect selecting data, only effecting display data. Once a patient meet that three requirements, the id will be used to search through all other tables to get all test values, no matter they have values or not, the data has to be returned.

 

[RTag]

What is the database type ? Do you have a query editor that can be used to run the SQL directly ?
You can try to analyze the SQL with the tools provided by the database. The other option is to run the SQL in a query editor and to remove the joins which are not filtered:

SELECT DISTINCT *
FROM MedDB.dbo.patient_encounter patient_encounter
INNER JOIN MedDB.dbo.master_im_ master_im_ ON patient_encounter.person_id = master_im_.person_id AND patient_encounter.enc_id = master_im_.enc_id
WHERE patient_encounter.enc_timestamp>= {ts '2011-01-01 10:31:33'}
AND patient_encounter.enc_timestamp<{ts '2011-12-31 00:00:01'}
AND master_im_.visit_type = 'Office Visit'
AND master_im_.dept = 'IM'
If this SQL runs faster , add the other joins one by one util you find which one is slowing the report.

Do you have numeric fields in master_im_ that can be used to filter the visit type and department? As it is now you are comparing strings which is costly.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[kate8]

RTag,

Thank you so much for your respond!!
I am using SQL Server 2008 R2 and I use SQL Server management Studio.It took 2 minutes to run the SQL you suggested. There are no numeric fields in master_im_ to replace the Detp and visit type. Actually , the report worked ok until join the Order_ table. I think it is not because this table, it is just because the report got so many left join and stuck in the last one -- order_. I tried to use subreport for the Order_ table, it ran completed (took long time). But the user needs to export to Excel data only format. With subreport, export to Excel Data only does not work.
What else can I try?
Again, thanks a lot!!

 

[lbass]

If you were willing to use a command as your datasource, you could speed up the report by moving the Where criteria to the From clauses. If you only need a subset of rows from left joined tables, you could similarly add criteria in the FROM clauses and your left joins would still be preserved. I have found this method to make a dramatic difference to report speed.

-LB

 

[RTag]

As far as I can see you are joining patient_encounter with order_ by person_id. There should be another column which will identify the orders related to one encounter record. For example: In master_im_ you have enc_id. As it is now you are retrieving the encounter records inside the period and then show all orders for the patient (even those outside the period) . Besides that this will return much more records, the report probably Is not correct. If there is no column like enc_id in orders_, you can include completedDate in your join clause like what lbass proposed:

LEFT OUTER JOIN MedDB.dbo.order_ order_ ON patient_encounter.person_id = order_.person_id and '1/1/2011'<= order_.completedDateAND order_.completedDate<'1/1/2012'

I guess table vital_signs_ should be processed in similar way


You have 2 very powerful options in SSMS to optimize the query:
- click menu Query -> "Display estimated Execution plan": this will show you what SQL server is planning to do. You can see the % of the resources taken by each action. You have also an option to use Query->"Include actual execution plan" which will run the query and will show you the actual execution plan , but as far as I understand you are not able to run the whole query. Estimated execution plan may be not precise - you may need to talk to your DBA to check if the statistics for the database were updated soon. It might make a huge difference if you just update the statistics of the tables involved in the query.

- Query -> "Analyze Query in Database Engine Tuning Advisor" - You can run this over the query before to add order_ table. The wizard will propose you to create indexes to improve the performance. You should talk to your DBA before to apply any changes to the database.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[kate8]

Thank you, LBass and RTag!!

RTag, I can't add order_.completedDate to the join. Because the report need display the patients who were seen in 2011 and their latest test which could be in 2011 and could be in 2009, 2012 etc. When they were seen in 2011, they might not have all the testes, so for those tables which store test result, I can only join with person_id, then bring huge data back to the report, display the latest test.
I will try what lbass suggested and your other suggestion and see.
Thank you!

 

[RTag]

Then you can use a subquery which will find the last order per patient before the end of 2011.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[kate8]

Hi lbass,
When you said " moving the Where criteria to the From clauses". is it should be like this:

SELECT DISTINCT "person"."last_name", "person"."first_name", "person"."middle_name", "person"."date_of_birth", "person"."sex", "vital_signs_"."BMI_calc", "Social_Hx_"."tobacco_type", "master_im_"."dept", "patient_encounter"."enc_timestamp", "person"."person_id", "health_maint_"."lastcolonoscopy", "health_maint_"."lastmammogram", "health_maint_"."lastfecaloccltx3", "Social_Hx_"."tobacco_yes", "provider_mstr"."last_name", "provider_mstr"."first_name", "patient_encounter"."supervisor_provider_id", "provider_mstr_1"."delete_ind", "provider_mstr_1"."last_name", "provider_mstr_1"."first_name", "person_external_xref"."external_id", "patient_"."pcp_lastname", "patient_"."pcp_1st", "vital_signs_"."bp_systolic", "vital_signs_"."bp_diastolic", "vital_signs_"."create_timestamp", "patient_diagnosis"."icd9cm_code_id", "order_"."actTextDisplay", "order_"."completedDate", "patient_"."warning", "order_"."obsInterpretation", "master_im_"."visit_type"
FROM (((((((((("MedDB"."dbo"."patient_encounter" "patient_encounter" INNER JOIN "MedDB"."dbo"."master_im_" "master_im_" ON ("patient_encounter"."person_id"="master_im_"."person_id") AND ("patient_encounter"."enc_id"="master_im_"."enc_id")AND
"master_im_"."dept"='IM - GMAP' AND ("patient_encounter"."enc_timestamp">={ts '2011-01-01 10:31:33'} AND "patient_encounter"."enc_timestamp"<{ts '2011-12-31 00:00:01'}) AND "master_im_"."visit_type"='Office Visit')
INNER JOIN "MedDB"."dbo"."person" "person" ON "patient_encounter"."person_id"="person"."person_id") INNER JOIN "MedDB"."dbo"."provider_mstr" "provider_mstr" ON "patient_encounter"."rendering_provider_id"="provider_mstr"."provider_id")
LEFT OUTER JOIN "MedDB"."dbo"."provider_mstr" "provider_mstr_1" ON "patient_encounter"."supervisor_provider_id"="provider_mstr_1"."provider_id")
LEFT OUTER JOIN "MedDB"."dbo"."patient_diagnosis" "patient_diagnosis" ON "patient_encounter"."person_id"="patient_diagnosis"."person_id")
LEFT OUTER JOIN "MedDB"."dbo"."order_" "order_" ON "patient_encounter"."person_id"="order_"."person_id"
AND "order_"."testCode"='12345' AND "order_"."completed"=1)
LEFT OUTER JOIN "MedDB"."dbo"."Social_Hx_" "Social_Hx_" ON "master_im_"."person_id"="Social_Hx_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."health_maint_" "health_maint_" ON "master_im_"."person_id"="health_maint_"."person_id") LEFT OUTER JOIN "MedDB"."dbo"."vital_signs_" "vital_signs_" ON "master_im_"."person_id"="vital_signs_"."person_id") INNER JOIN "MedDB"."dbo"."patient_" "patient_" ON "person"."person_id"="patient_"."person_id")
LEFT OUTER JOIN "MedDB"."dbo"."person_external_xref" "person_external_xref" ON "person"."person_id"="person_external_xref"."person_id"

I ran it under SSMS for a hour, still no result returing. I stoped it. Is there something wrong here?

 

[lbass]

I would have ordered the table criteria together:

FROM (((((((((("MedDB"."dbo"."master_im_" "master_im_" inner join MedDB"."dbo"."patient_encounter" "patient_encounter" ON ("master_im_"."person_id"="patient_encounter"."person_id") AND
("master_im_"."enc_id"="patient_encounter"."enc_id") AND
"master_im_"."dept"='IM - GMAP' AND
"master_im_"."visit_type"='Office Visit' and
(
"patient_encounter"."enc_timestamp">={ts '2011-01-01 10:31:33'} AND
"patient_encounter"."enc_timestamp"<{ts '2011-12-31 00:00:01'})
)

Also, I think you should be leading FROM the master table TO the patient encounter table, assuming there is one record in the master table. Where possible (and logical), link the other tables to the Master table instead of the patient encounter table--to reduce the number of records.

-LB