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

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
    ACCEPTED ANSWER

    Re: Performance of a SELECT Statement

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

      Re: Performance of a SELECT Statement

      ‏2013-02-26T11:39:22Z  in response to IBM_MAN
      Dear IBM Man,

      thanks a lot for your help!

      Kind regards
      • SystemAdmin
        SystemAdmin
        3105 Posts
        ACCEPTED ANSWER

        Re: Performance of a SELECT Statement

        ‏2013-02-26T15:32:36Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Performance of a SELECT Statement

          ‏2013-02-28T11:52:05Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

            Re: Performance of a SELECT Statement

            ‏2013-03-01T20:14:32Z  in response to SystemAdmin
            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
            ACCEPTED ANSWER

            Re: Performance of a SELECT Statement

            ‏2013-03-09T22:43:52Z  in response to SystemAdmin
            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?