Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    334

    Unanswered: how to improve like predicate performance?

    hello , everyone:

    I have faced a performance problem:
    Suppose table T1 has about 2M rows and has two columns :
    custid char(21), custname char(60)

    Here is a query
    select * from t1 where custname like '%ABCD%'
    It tooks about 30 seconds now

    Is there any idea to finish this query within 10 seconds?

    THks, Any suggestion will be appreciated...

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Taak a close look at the runstats command. Search for the "LIKE STATISTICS". (If you measure any improvements, please report that back to us).

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Is it always the same pattern used in the LIKE predicate? If so, you can add another column (possibly indexed) which stores the result of the LIKE predicate. If you have varying patterns in your queries, then the string comparison has to be done on each row.

    The pattern you have shown is only doing a simple find and no pattern matching is needed. So you may want to consider using the built-in functions POSSTR() and or LOCATE() instead.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you can convice your users to accept
    like 'ABCD%'
    and you have an index on that column, it will run much faster.

    Even if they sometimes need like '%ABCD%' if they only need it occassionly (and can specify when they need it), that will save a lot of time when they know the leading string, instead of the string being anywhere in the column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    hi , thanks for all of your response。

    dr_te_z ,
    I tried "like statistics" ,but it didn't work。I think the problem is not on card underestimate。The step cousuming most time is to compare each rows' custname with the like patten。

    Marcus_A ,
    we cannot change the condition to like 'abcd%' because users said they cannot always remember the leading string of the custname.

    Stolze.,
    the posstr works! It reduce the excuting time to about 5 seconds. But i don't know why?
    because it also have to do string comparison row by row, can you explain it for me , thanks.
    And i have do some reading on the your article <Basic text indexing with DB2 index extensions>,but it looks so complicated and there are too many places i can't understand .....
    can we write some udf like that to solve this problem........

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Naturally, pattern matching is much more complicated than a simple POSSTR(). Basically, POSSTR() could be implemented internally by DB2 using a system functions like strchr() and strcmp(). (It is a bit more complicated than that, but you get the gist.) With pattern matching, you may have wildcards at any arbitrary place in the pattern, backtracking may be needed, etc. LIKE predicates have to use pattern matching. So much more processing is needed in this case, even if the result is the same as a simple POSSTR() in your specific situation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Thank you very much for your explaination!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •