We are using openmrs-module-htmlformentry to create and display forms on UI.
We have a huge form with around 137 obs tags and 33 obsgroup tags.
It takes around 45 seconds for the server to create and render the html on the browser.
A fraction of a second is taken to load the <form_template>.xml file. However, around 44 seconds is taken to convert the template into an html page and render on the UI. All this is happening in the server.
The DB server trace was enabled and the queries sent to the MySql DB was captured in the attached query.log file.
In the logs we found that there are around 1295 complex queries of concept tables and the where clause had UUID in it. The joins occurs on concept, concept_numeric and concept_complex tables.
Also in the logs, we found 111 queries on concept_answer table with conceptId in WHERE CLAUSE.
There are 32 repeat tags to display various checkbox questions. Removing ârepeatâ tag improve performance. But we want to follow the openmrs standards.
Core Issue:
Clearly for every obs and obsgroup tag, there is a DB query going to the DB server. This is slowing down the loading of the form. We need to either change the behavior of the âobsâ related tag, not to go to the DB every time or we need to cache the hibernate calls. The openmrs community has the following topic but there are pros and cons related to this approach - What is the best way to cache OpenMRS Concept server side
Questions:
In which module of openmrs, is the actual Java ORM implementation of âobsâ and âobsgroupâ tag? I tried going through all âhtmlformentryâ related modules but could not find what I was looking for. If anyone has worked on it before and can help me, that would be great. I can debug and research more on the origin of the calls.
Has anyone faced this type of problem before? If yes, what approach was used to tackle this problem? Hibernate second level cache seems to a solution given in the link above, but how do we ensure that the new added concepts are added to the cache?
What are the alternative openmrs standards for using Checkboxes, apart from using repeat tag.
You say âWe have a huge form with around 137 tags and 33 tagsâ⊠I assume thereâs a typo here?
When you said âremoving ârepeatâ tag improved performanceâ, how much did it improve the performance?
Itâs true that each Obs tag on the form will result in one or more calls to the database to fetch the concepts related to the obs, so itâs certainly possible that there may be 1000+ queries to the database to fetch concepts based on the number of Obs tags + repeats you have. Iâd be interested in hearing if others have had forms of this size and we are just maxing out on the number of Obs a single form can handle performantly under the current design (with each Obs call fetching each concept individually) or if perhaps thereâs something else going on in this specific case.
If it would be possible for you to share your form as well, that would be helpful.
You can find the code that handles the Obs and ObsGroup tags within the htmlformentry module, and specifically the place where the Obs tag loads concepts can be found here:
@manek, if you could share your form (especially if i can run exclusively using CIEL or a known dictionary, or if you have a distribution that we can try it with), or if you can test out loading the form while attached to a profiler like YourKit or similar and share the output of that, it would be very helpful.
@mseaton and @mogoodrich
Thanks for your prompt replies. I will sharing my form after making changes and using CIEL exclusively shortly. My apologies for the delay as the form is big and I have to make changes at each place manually.
Thatâs great @sameermotwani11! In the sense that it helps so narrow down the performance problems.
Just wondering, from your testing, have you confirmed that itâs the repeat tag in conjunction with the mappings, or just the mapping themselves? (We should fix regardless, but it narrows down where to look).
Can you confirm that thereâs a key/index on the âcodeâ column of the concept_reference_term table?
@sameermotwani11 hereâs the code that handles the repeat tag⊠I just glanced at it quickly, but nothing strikes my eye that would make it perform differently when using mappings vs uuids:
The following query is created by Hibernate and which slows the form. If we only use uuid, then only the PK is fetched and forms load very fast:
select this_.concept_id as y0_ from concept_reference_map this_ inner join concept concept3_ on this_.concept_id=concept3_.concept_id left outer join concept_numeric concept3_1_ on concept3_.concept_id=concept3_1_.concept_id left outer join concept_complex concept3_2_ on concept3_.concept_id=concept3_2_.concept_id inner join concept_reference_term term1_ on this_.concept_reference_term_id=term1_.concept_reference_term_id inner join concept_reference_source source2_ on term1_.concept_source_id=source2_.concept_source_id where lower(term1_.code)=â1015â and (lower(source2_.name)=âcielâ or lower(source2_.hl7_code)=âcielâ) order by concept3_.retired asc
In order to move on with this it would help others to reproduce the faulty behaviour by having the most minimal sample form that produces such slowness on the demo. Is that something you guys can put together?
I just tried to save the form in Repeat_with mappings_FORM.txt on the demo and got this:
Error! concept (CIEL:300) does not exist
Error! concept (1970697) does not exist
Error! concept (1970695) does not exist
Error! concept (1970537) does not exist
Error! concept (1970693) does not exist
Error! concept (1970659) does not exist
Error! concept (1970712) does not exist
Ah! Thanks for sharing @manek ⊠I see something in this query thatâs a bit of a red flag⊠the âlowerâ mysql function⊠weâve had issues in the past because using âlowerâ removes the benefit of indexing⊠for example, see this old ticket:
Can you confirm that the following GP is set to false on your system:
If this is set to false, I believe that it shouldnât display the âlowerâ in the query⊠and, if it still does, thereâs likely a bug⊠(that Iâd definitely want to look into, because it may be affecting us as well).
If you do set the variable to false, you will need to make sure that the collation setting on mysql is set to case-insensitive (ci)⊠I donât remember exactly how to do this, but we explicitly set the collation to ci in our mysql.cnf:
After changing the GP to FALSE, the time has decreased drastically. This is a huge help. Thanks a ton!
New Query:
select this_.concept_id as y0_ from concept_reference_map this_ inner join concept concept3_ on this_.concept_id=concept3_.concept_id left outer join concept_numeric concept3_1_ on concept3_.concept_id=concept3_1_.concept_id left outer join concept_complex concept3_2_ on concept3_.concept_id=concept3_2_.concept_id inner join concept_reference_term term1_ on this_.concept_reference_term_id=term1_.concept_reference_term_id inner join concept_reference_source source2_ on term1_.concept_source_id=source2_.concept_source_id where term1_.code=â21â and (source2_.name=âCIELâ or source2_.hl7_code=âCIELâ) order by concept3_.retired asc
In the sense that it helps so narrow down the performance problems.