ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Efficient text search

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Efficient text search

    We have a search function the business use. It does an SQL search like this (only with more joins)
    Code:
    select * from Clients
    where upper(CompanyName) like '%SEARCHTERM%' or upper(TradingName) like '%SEARCHTERM%'
    I.e. it searches if either field contains the search term, case insensitive

    Our Ops guys are complaining because it is run hundreds of times a day and uses a notable amount of CPU. IN worse cases it can take over 70 seconds to run, which can make the users think it has failed and rerun it.

    Anyone have suggestions on how to speed it up? All the Index Advisor will say is to create EVI indexes on the two columns, which seems like a bad idea as they are largely distinct values, which is not what EVI indexes are for

  • #2
    I'm no SQL guru by any stretch, but could the "LIKE" be replaced by "="?

    Cheers,

    Emmanuel

    Comment


    • #3
      You could try making the SQL run time option Sort Sequence = *LANGIDSHR to make string comparisons case insensitive and get rid of the upper( ).
      And replace the LIKEs with locate('SEARCHTERM' , xxxxxxName) > 0. Might not help but worth a try. And do you really need all columns * ? Just select the columns needed.

      Ringer

      Comment


      • #4
        the UPPER(Field) and even worse the LIKE '%VALUE%' will outperform the query.
        The optimizer cannot use any binary radix tree index for this kind of expression.
        If it would be only the UPPER(Field) and a LIKE with the % placeholder at the end, it is possible to create a derived index (with the key colum UPPER(Field). The optimizer can use this kind of index.

        You may be able to create OMNIFIND Text Search Indexes, but these are tricky and will be installed in the IFS.
        For more information about OMNIFIND Text Search look at the following Link
        OmniFind Text Search Server for DB2 for i allows you to issue SQL statements that satisfy familiar text search queries on documents that are stored in a DB2 database.


        Birgitta

        Comment

        Working...
        X