Tags: afterthe, column, database, db2, index, likepredicate, matching, mins, mysql, oracle, pattern, predicate, search, slow, sql

Pattern Matching using LIKE predicate on a DB2 column using INDEX

On Database » DB2

5,853 words with 2 Comments; publish: Sat, 24 May 2008 15:04:00 GMT; (25058.59, « »)

I have a column in DB2 with an index. When I search using LIKE

predicate,

sometimes it gives results very slow (about 3 to 5 mins). But after

the initial hit,

subsequent searches using different expressions return very fast (2

secs).

It is almost as-if DB2 decides to cache the whole index when we run

the

query first time.

After couple of hours later (or next day) again the initial query

takes the hit.

Any ideas ?

All Comments

Leave a comment...

  • 2 Comments
    • <govindrjujare.db2.itags.org.gmail.com> wrote in message

      news:1194399689.880857.184960.db2.itags.org.o80g2000hse.googlegroups.com...

      > I have a column in DB2 with an index. When I search using LIKE

      > predicate,

      > sometimes it gives results very slow (about 3 to 5 mins). But after

      > the initial hit,

      > subsequent searches using different expressions return very fast (2

      > secs).

      > It is almost as-if DB2 decides to cache the whole index when we run

      > the

      > query first time.

      > After couple of hours later (or next day) again the initial query

      > takes the hit.

      > Any ideas ?

      All data and index pages are cached in the bufferpool(s). If a needed data

      or index page is not already in the bufferpool, then DB2 puts it in the

      bufferpool, and then processes the query after that. After a while, other

      SQL statements will force old pages out of the bufferpool if the bufferpool

      is smaller than the sum of all the data and indexes. You should make the

      bufferpool(s) as large as possible so long as you have real memory (not

      virtual memory) available on the DB2 server.

      However, I don't think you can account for a difference of 3-5 minutes

      versus 2 seconds based on bufferpool caching.

      In order to DB2 to use a b-tree of an index (the fasted access path), you

      must only use this kind of expression in a LIKE statement:

      'ABC%'

      Notice that you are supplying the leftmost bytes (1-3) of the key, and the

      rest are wildcards.

      If you use '%ABC%' then DB2 cannot use the b-tree and must scan the entire

      index, or do a table scan. So if possible it best to limit the search string

      to always supplying the leftmost bytes if that meets your application

      requirements.

      #1; Sat, 24 May 2008 15:05:00 GMT
    • On Nov 6, 11:36 pm, "Mark A" <nob....db2.itags.org.nowhere.com> wrote:

      > <govindrjuj....db2.itags.org.gmail.com> wrote in message

      > news:1194399689.880857.184960.db2.itags.org.o80g2000hse.googlegroups.com...

      >

      >

      >

      >

      >

      >

      > All data and index pages are cached in the bufferpool(s). If a needed data

      > or index page is not already in the bufferpool, then DB2 puts it in the

      > bufferpool, and then processes the query after that. After a while, other

      > SQL statements will force old pages out of the bufferpool if the bufferpoo

      l

      > is smaller than the sum of all the data and indexes. You should make the

      > bufferpool(s) as large as possible so long as you have real memory (not

      > virtual memory) available on the DB2 server.

      > However, I don't think you can account for a difference of 3-5 minutes

      > versus 2 seconds based on bufferpool caching.

      > In order to DB2 to use a b-tree of an index (the fasted access path), you

      > must only use this kind of expression in a LIKE statement:

      > 'ABC%'

      > Notice that you are supplying the leftmost bytes (1-3) of the key, and the

      > rest are wildcards.

      > If you use '%ABC%' then DB2 cannot use the b-tree and must scan the entire

      > index, or do a table scan. So if possible it best to limit the search stri

      ng

      > to always supplying the leftmost bytes if that meets your application

      > requirements.

      That is what I thought. I thought the search would depend on how much

      of

      leftmost and rightmost parts of the key I have specified. But my db2

      instance

      is behaving kind of odd. It happened today again. I first ran a query

      like the

      following which took 3 minutes -

      select indexkey from table where indexkey like '_____-

      ______'

      Now subsequent searches take 4 seconds.

      select indexkey from table where indexkey like '__441_____%'

      select indexkey from table where key indexkey '_______ABC%'

      select indexkey from table where indexkey like '%987%'

      Even if I give an expression that is not present, it comes back in 4

      seconds.

      OTOH, I have another table on which the key has not been indexed.

      Pattern

      matching queries on that table always yield results in predictable

      (takes minutes)

      amount of time (even equal-to comparison takes almost same number of

      minutes

      +/- some seconds).

      #2; Sat, 24 May 2008 15:06:00 GMT