Topic
  • 6 replies
  • Latest Post - ‏2013-03-09T22:43:52Z by SystemAdmin
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic Performance of a SELECT Statement

‏2013-02-25T15:52:15Z |
Hi all,

here is my doubt: I´m working with a table which is accessed in read mode several times with different queries. It is not possible to create an index for each query, because there would be too many indexes.
These queries have several predicates in the WHERE clause like:

SELECT FIELD1, FIELD2
FROM TABLE1
WHERE FIELDA = XXX AND
FIELDB = BBB AND
FIELDC = ZZZ AND
FIELDD = AAA

My particular question is:

  • In a WHERE clause of a SELECT statement, should the predicates follow the same order of the fields in the table to be more efficient?
Updated on 2013-03-09T22:43:52Z at 2013-03-09T22:43:52Z by SystemAdmin
  • IBM_MAN
    IBM_MAN
    2 Posts

    Re: Performance of a SELECT Statement

    ‏2013-02-25T17:45:23Z  
    No, order of where clause columns doesn't matter. DB2 Optimizer is smart enough to figure that out.
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Performance of a SELECT Statement

    ‏2013-02-26T11:39:22Z  
    • IBM_MAN
    • ‏2013-02-25T17:45:23Z
    No, order of where clause columns doesn't matter. DB2 Optimizer is smart enough to figure that out.
    Dear IBM Man,

    thanks a lot for your help!

    Kind regards
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Performance of a SELECT Statement

    ‏2013-02-26T15:32:36Z  
    Dear IBM Man,

    thanks a lot for your help!

    Kind regards
    that is true for simple predicates
    if you have correlated subqueries in your where clause, order does matter

    you should however make sure your indexes are defined correctly
    for the sample statement you provided, an index on all 4 columns will provide most benefit

    if you provide several other statements that you use, I can try to come up with the list of indexes that will give you the best coverage
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Performance of a SELECT Statement

    ‏2013-02-28T11:52:05Z  
    that is true for simple predicates
    if you have correlated subqueries in your where clause, order does matter

    you should however make sure your indexes are defined correctly
    for the sample statement you provided, an index on all 4 columns will provide most benefit

    if you provide several other statements that you use, I can try to come up with the list of indexes that will give you the best coverage
    Thanks a lot momi,

    but this paricular table is accessed many times in several different ways; our aim is to optimize it for INSERT purposes. If we create an index for each query with these types of predicates, the performance would be seriously compromised.
  • Rob_Strong
    Rob_Strong
    5 Posts

    Re: Performance of a SELECT Statement

    ‏2013-03-01T20:14:32Z  
    Thanks a lot momi,

    but this paricular table is accessed many times in several different ways; our aim is to optimize it for INSERT purposes. If we create an index for each query with these types of predicates, the performance would be seriously compromised.
    I probably misunderstand your recent post, ejimeneh, but I will mention: a single index on a table can be used by tens, hundreds, thousands of very different queries.

    Generally, no special authorization is required for DB2 to use an index to find data in a table (the authorization is just to the table); if an index exists and matches the query, and the DB2 optimizer determines that it will be effective (which generally happens without direction by the user), the index will be used. Creating a few "good" indexes, ones that match well a lot of different queries or a few important queries, is very important.

    Yes, too many indexes can adversely affect INSERT/UPDATE/DELETE performance, but not having those few "good" indexes can also greatly and negatively affect performance, not just for SELECTs but also for UPDATEs and DELETEs.

    Indexing is one of the most important aspects of application/table design.
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Performance of a SELECT Statement

    ‏2013-03-09T22:43:52Z  
    Thanks a lot momi,

    but this paricular table is accessed many times in several different ways; our aim is to optimize it for INSERT purposes. If we create an index for each query with these types of predicates, the performance would be seriously compromised.
    Why are you trying to optimize it for inserts?
    What is the inserts / selects ratio?
    is this a log table?
    have you considered using the append option?