Notifications
Clear all
Topic starter
Since we have upgraded to Centricity EMR 9.10 our reports that have commands in them have become so slow they are not worth running, a user complained of waiting 30 minutes for a fairly simple report. The command isn't even all that complicated, get a list of our active Mid Level users:
SELECT SEARCHNAME FROM
ML.USRINFO WHERE
(JOBTITLE = 1 or JOBTITLE = 3 or JOBTITLE = 1635153427007890 or JOBTITLE = 1737548329372760) AND STATUS = 'A'
The report looks for documents that were started under the Mid Level, for the given date range, and then looks for certain contributions by anyone else with a different NPI than the selected user.
Has anyone else noticed this since they have upgraded to 9.10? I also noticed that if I left on the auto key link in crystal reports it took significantly longer to load the links tab. Did the structure/indexes for the database change from 9.8.12 to 9.10?
Any help/advice would be greatly appreciated!
- Noah
Posted : October 5, 2017 2:32 am
Query design is the issue. If you are selecting on an unindexed field such as as ClinicalDate converted to a date value checked against a date parameter rather than ClinicalDate checked against a date parameter converted to ClinicalDate format, then it has to step through every Document. I'd be glad to show you if you are interested. Contact me directly. It can result in a factor of 10 speed improvement.
Posted : October 5, 2017 4:22 am
We did not notice a speed difference. Like jfitzmd mentioned, it is likely how you are searching for you data. If you open the report in crystal and run it does it take a long time? If so, can you run the report and then click on "Database -> Show SQL query" and paste that query here? Alternatively if you upload the report I can get it from there.
I also wanted to point out that the query you posted here could be better written as:
JOBTITLE IN (1,3,1635153427007890,1737548329372760)
Posted : October 6, 2017 5:56 am
