I see. Thank you for the clarification. Wyclif, can you please open a
ticket for this since you were the one that suggested the solution?
I still think someone should look into use of ilike in other methods to
evaluate the performance hit.
Thanks,
Tammy
On 10/7/2011 3:34 PM, Burke Mamlin wrote:
Get concepts should be using concept_word to search by name, in which
case – as Wyclif suggests – using like + name.toUpperCase() should
suffice.
-Burke
On Fri, Oct 7, 2011 at 3:10 PM, Tammy Dugan <[email protected]
<mailto:[email protected]>> wrote:
I understand that, Darius, but the sql that runs for ilike with
MySQL is unacceptably slow. Either there needs to be special
processing for each ilike in the case that MySQL is the database
or otherwise another solution needs to be used other than ilike.
One example would be to have an additional column in the concept
name table with a a lowercase version of the concept name and make
sure the search phrase is changed to all lowercase before it is
passed to the sql. I know the exception is inconvenient but it is
necessary. Without the exception, the current version of logic is
unusably slow for us.
Thanks,
Tammy
On 10/7/2011 2:52 PM, Darius Jazayeri wrote:
Hi Tammy,
We're not coding specifically against MySQL though. So we do in
fact need to use ilike.
I agree that like + matchmode=exact should never be used.
-Darius
On Friday, October 7, 2011, Tammy Dugan wrote:
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]> 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
------------------------------------------------------------------------
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]
