<Obs> html tag calls DB every time to fetch concept mapping.

Issue -

  • 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:

  1. 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.

  2. 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?

  3. What are the alternative openmrs standards for using Checkboxes, apart from using repeat tag.

Tagging Mike Seaton @mseaton and Mark Goodrich @mogoodrich

Tagging @mksd @sameermotwani11

Tagging @mukesh and @paulamendola

1295-query.txt (4.0 MB)

@manek… thanks for all the info!

  • 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:

Take care, Mark

@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.

Thanks, Mike

@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.

Regards, Manek

1 Like

Hi All, I was able to perform good experiment using different permutations and combinations. Following are the results:

  1. Repeat with CIEL Mapping Ids = 11.5 minutes to load form.
  2. Repeat with ConceptIds = 2.52 seconds to load form.
  3. Repeat with UUID’s = 2.98 second to load form.

It looks like that when we use Repeat tag with Mappings, it take exponential time to load the form.

Repeat_with_Ids_FORM.txt (40.7 KB) Repeat_with mappings_FORM.txt (42 KB) Repeat_WITH_UUID_FORM.txt (49.3 KB)

Just to confirm, is that really 11.5 minutes vs 2.52 seconds?

You read that right mark. We were shocked too. The issue is with using mappings in Repeat Tag.

That’s great @sameermotwani11! :slight_smile: 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?

Take care, Mark

#1 It’s the repeat tag in conjunction with the mappings. #2 There is an index in the code column

We’ll continue to investigate. :slight_smile:

1 Like

@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:

Screenshot%20from%202019-04-04%2016-46-31

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:

character-set-server=utf8
collation-server=utf8_general_ci

I think this is all that is needed, but I’m not sure if you have to “apply” this collation change to existing tables somehow.

Take care, Mark

2 Likes

@mogoodrich You are the best!

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

@mogoodrich genius!