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
--
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 from OpenMRS Developers' mailing list
Click here to unsubscribe from OpenMRS Developers' mailing list
--
Tammy Dugan
CHIRDL Technical Lead
Children's Health Services Research
IU School of Medicine
Click here to unsubscribe 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]
