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]<javascript:_e({}, 'cvml', '[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", 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 <javascript:_e({}, 'cvml', > '[email protected]?body\x3dSIGNOFF%20openmrs-devel-l');> from > OpenMRS Developers' mailing list > > > Click here to unsubscribe <javascript:_e({}, 'cvml', > '[email protected]?body\x3dSIGNOFF%20openmrs-devel-l');> from > OpenMRS Developers' mailing list > > > -- > Tammy Dugan > CHIRDL Technical Lead > Children's Health Services Research > IU School of Medicine > > ------------------------------ > Click here to unsubscribe <javascript:_e({}, 'cvml', > '[email protected]?body\x3dSIGNOFF%20openmrs-devel-l');> from > OpenMRS Developers' mailing list _________________________________________ 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]
