For today’s Techie Tuesday tip, I will continue the series: How to Turn the 5 Things You Hate About NPR Report Writing into the 5 Things You Love.
Today’s topic: My Report Is Slower Than A Snail In Peanut Butter!
MEDITECH is fast at reading data from the database
The following are examples of items that can effect the efficiency of a report and result in slowness:
- Not selecting the best index for the report
- Sort Fields/Grouping – avoid if possible using too many sort fields as these can effect the speed of your report
- Utilizing fragments can result in slowness
- Defining Select Relationships
Indexes… Indexes… Indexes…
Index Files Improve Report Efficiency
- Choose index files to reduce number of records searched
- Select index for records searched, not sort order
- Use natural sort order
- Use index file’s subscript as a select
Indexes… Choose Them Wisely
Try to find an index where the first field in the index results in the greatest reduction of returned records.
For example, we want to create a report that prints all final billed patients discharged within a specific timeframe. In BAR, there are two available indexes:
- BAR.PAT.bar.status.x – this index returns a list of accounts based on the bar status of the account. The first field of the index is bar.status.
- BAR.PAT.discharge.x – this index returns a list of accounts based on the discharge/service date. The first field of the index is bar.dis.ser.
Which is the best index to use and why?
BAR.PAT.bar.status.x OR BAR.PAT.discharge.x
Larger time-frame: If BAR.PAT.bar.status.x is utilized where the report selects all accounts where bar.status = ‘FB’ then the report will loop through all final billed accounts and then filter by discharge date.
Smaller time-frame: If the report is running for a relatively small time-frame, then BAR.PAT.discharge.x is the best index to use. There will typically be fewer patients discharged during the specified time-frame than all final billed patients in the hospital system.
- Useful for crossing applications
- Gathering data
- Can return one field to entire lines of report text
Fragments can be SLOW:
Reports with a high number of records (thousands) or those called at the detail level may experience performance issues because the application prefixes are constantly opened and closed.
Redesigning your report can avoid these issues.
The Reason Fragments are Slow:
- Each called Fragment opens its own report process (memory usage, job number, etc.)
- Each time a Fragment is called:
- The main reports is paused
- A new job is setup
- The application database is open
- The Fragment is open and running
- The Fragment report is closed
- The application is closed
- The job ends
- Control returns to the main report
When it is best to NOT call a Fragment:
- When it needs to be called frequently (every detail section, every HK3, etc.)
- When there is extensive sorting in both the fragment and main report
- When you need to print in both the main report and fragment:
- Makes formatting more difficult
- Changes in either report can mess up the other
When to use Fragments:
- When the Fragment is called only once:
- At START to get data
- At TR, with all the printing done in the fragment
- At Detail for simple reports, where the delay is not an issue:
- Where the user selects a specific record (one patient, PO#, a couple of mnemonics, etc.)
- At major sort regions for long reports (HK1 for account types, at physician sort when the users typically only select a few, etc.)
If fragments are slow, then how can I cross applications without using a fragment?
Utilize macros in order to open prefixes to a specified application and retrieve the desired results which can then be displayed on the report.
Defining Select Relationships
Utilize computed fields as an alternative to defining select relationships. This is faster than applying OR statements in the report criteria via select relationships.
For example, we want a report to return all patients discharged during a specified time-frame with either a final billed or unbilled status.
Option 1 (Slower): Create two selects fields where one checks if bar.status is equal to ‘FB’ and the other checks if bar.status is equal to ‘UB’. Set the Select Relationship to perform an ‘OR’ so that if the bar status is either ‘FB’ or ‘UB’ (and discharged within the specified time-frame) then the account is returned.
Option 2 (Faster): Create a calculated field that checks if the bar.status is set to either ‘FB’ or ‘UB’. If the criteria is true, then ‘Y’ is returned; otherwise ‘N’ is returned.