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]> 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", 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 > > _________________________________________ 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]
