Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001

    Unanswered: How to improve performance when using distinct

    Very simple statement:
    select distinct locid from location

    The table contains a million of rows, and the statement only return 1-3 records (i.e. the table only have 1-3 sets of locid). Index (locid) is built in the table, it takes 3-7 seconds to run this SQL, very slow, I have tried to build clustering index on locid, it helps a little, but still very slow, take 2-5 seconds to run. Is there any way to improve the performance of this SQL?

    Thx in advance.

  2. #2
    Join Date
    Jan 2003
    Are you assuming that the duration of a query should be related to the queries output in terms of rows returned?
    2-5 seconds does not seem unreasonable. I have a 14 second wait for a similar query fetching 4 distinct years from a 2.5 million table.

    However query optimization is set at 9, which may be okay for complicated queries and bad for simple ones (Optimizer is given more time to choose an access path)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    SQL, SQL server

  3. #3
    Join Date
    May 2003
    In order to satisfy the query, DB2 has to read all the rows of the index. Even though the column length of the index may be small, 4 million is a lot of index rows to read.

    Most studies have shown that optimization level 7 is about as high as you want to go except in very rare cases. Optimization level 9 can take a lot longer than level 7.

  4. #4
    Join Date
    Dec 2002

    Re: How to improve performance when using distinct

    You have only one option - build a summary table. You will have whole set of new issues like maitaining the table(when to refresh), but it's a different story.

Posting Permissions

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