We have been testing CHICA with 1.7.x openmrs and have run into some
performance issues. It takes 4 or more times longer to create a form in
1.7.x openmrs than 1.5 openmrs. We did some profiling this week and
found the major culprit was this line in getConcepts:
criteria.add(Expression.ilike("names.name", name, matchmode));
If we change ilike to like, getConcepts runs over 6 times faster (60
milliseconds verses 450 milliseconds). Making this change vastly
improved our timings. The reason this causes a problem is that MySQL
columns are case insensitive by default so "like" is case insensitive.
When ilike is used in the Expression it translates to "lower(name)".
Applying the lower function makes the query considerably slower. I know
that this line is supposed to work across multiple databases other than
mysql, thus the ilike. However, it causes an unacceptable performance
hit for those that use MySQL. Is there a way to identify whether the
database being used is MySQL and add some logic to use like instead of
ilike when MySQL is the database? Also, I noticed when like is used in
exact mode for MySQL it is twice as fast to use "=". It would be a good
idea to use that performance optimization as well.
Thanks,
Tammy Dugan
--
Tammy Dugan
CHIRDL Technical Lead
Children's Health Services Research
IU School of Medicine
_________________________________________
To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to
[email protected] with "SIGNOFF openmrs-devel-l" in the body (not
the subject) of your e-mail.
[mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l]
