I also want to point out that ilike is used in other places in the code.
I think all instances should be changed because every time it is used it
causes a performance hit in MySQL. Using like, exact match instead of
"=" is also a unnecessary performance hit wherever it is used.
Thanks,
Tammy
On 10/7/2011 2:10 PM, Wyclif Luyima wrote:
Concept words are converted to uppercase before they get saved, so a
quick possible solution would be to edit getConcepts to convert the
search phrase to uppercase before executing the query and then switch
ilike to like.
Wyclif
On Fri, Oct 7, 2011 at 1:51 PM, Darius Jazayeri <[email protected]
<mailto:[email protected]>> wrote:
I thought the whole point of ConceptWord was that we looked at
that rather than doing a query like the one Tammy mentions.
(Tammy, which version of getConcepts is that?)
-Darius
On Friday, October 7, 2011, Tammy Dugan wrote:
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
<http://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]
Click here to unsubscribe
<mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l> from
OpenMRS Developers' mailing list
Click here to unsubscribe
<mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l>
from OpenMRS Developers' mailing list
--
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]
