Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    23

    Question Unanswered: Any performance suggestions with this long running subselect?

    Hi everybody,

    We can't seem to figure out how to get the subselect in this query to use the index. It's very frustrating.

    Here is what happened when I ran runstats:

    1. Query without runstats ever being run: 37 TRILLION timerons (unbearable response time)
    2. Query with runstats on only zip code table: 2,000 timerons (very very good response in the application -- nearly instant)
    3. Query with runstats on the entire database: 40,000 timerons (application is very sluggish in returning results now -- 5 to 10 seconds wait time)

    What is frustrating is that the optimizer simply has no idea how to use the index in the subselect. It is executing a full table scan to return the data in that subselect step. The rest of the query executes very well.

    For some reason I can't get it use the index! Frustrating!!! Any tips?

    Maybe there is a way to flush out the stats for the other table we are joining with?

    We are using Redhat 7.3 Linux and DB2 V 7.2 Fixpack 8. Table is appx. 800,000 rows long. (Includes both US and Canadian zip codes). Zip code column is varchar(6).

    select
    s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance
    from
    store_location s, zip_code z,
    ( select latitude, longitude from zip_code where zip_code = '92704' ) loc
    where
    s.zip_code = z.zip_code and s.retailer_type_id=0
    order by
    distance

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Any performance suggestions with this long running subselect?

    Hi iceman,

    flushing out statistics should be regarded as a very last resort.

    Did you try rewording your query like e.g.

    select
    s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance
    from
    store_location s, zip_code z, zip_code loc
    where
    s.zip_code = z.zip_code and s.retailer_type_id=0
    and loc.zipcode = '92704'
    order by
    distance

    or:

    with loc as ( select latitude, longitude from zip_code where zip_code = '92704' )
    select
    s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance
    from
    store_location s, zip_code z, loc
    where
    s.zip_code = z.zip_code and s.retailer_type_id=0
    order by
    distance

    and check if any of this changes the execution path? At the least I would expect to see the with clause variation use the index.

    Johann

    Originally posted by iceman
    Hi everybody,

    We can't seem to figure out how to get the subselect in this query to use the index. It's very frustrating.

    Here is what happened when I ran runstats:

    1. Query without runstats ever being run: 37 TRILLION timerons (unbearable response time)
    2. Query with runstats on only zip code table: 2,000 timerons (very very good response in the application -- nearly instant)
    3. Query with runstats on the entire database: 40,000 timerons (application is very sluggish in returning results now -- 5 to 10 seconds wait time)

    What is frustrating is that the optimizer simply has no idea how to use the index in the subselect. It is executing a full table scan to return the data in that subselect step. The rest of the query executes very well.

    For some reason I can't get it use the index! Frustrating!!! Any tips?

    Maybe there is a way to flush out the stats for the other table we are joining with?

    We are using Redhat 7.3 Linux and DB2 V 7.2 Fixpack 8. Table is appx. 800,000 rows long. (Includes both US and Canadian zip codes). Zip code column is varchar(6).

    select
    s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance
    from
    store_location s, zip_code z,
    ( select latitude, longitude from zip_code where zip_code = '92704' ) loc
    where
    s.zip_code = z.zip_code and s.retailer_type_id=0
    order by
    distance

  3. #3
    Join Date
    Apr 2003
    Posts
    23
    Thanks jsander. I thought we had a winner for sure but it appears the execution time is the same for both of the queries.

    Man! I know I am getting close but just need to get DB2 the magic key!


    SQL Statement:

    with loc as (
    select latitude, longitude
    from zip_code
    where zip_code = '92704' )
    select s.name, s.address1, s.address2, s.city, s.state, s.zip_code,
    s.country, s.area_code, s.phone_number, s.products,
    round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-l
    oc.latitude))
    +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitud
    e)))), 1) distance
    from store_location s, zip_code z, loc
    where s.zip_code = z.zip_code and s.retailer_type_id=0
    order by distance


    Estimated Cost = 38012
    Estimated Cardinality = 1478

    Access Table Name = DB2INST1.ZIP_CODE ID = 2,96
    | #Columns = 3
    | Single Record
    | Index Scan: Name = DB2INST1.PK_ZIP_CODE ID = 1
    | | Index Columns:
    | | | 1: ZIP_CODE (Ascending)
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | 1: '92704'
    | | | Stop Key: Inclusive Value
    | | | | 1: '92704'
    | | Data Prefetch: None
    | | Index Prefetch: None
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    Nested Loop Join
    | Piped Inner
    | Access Table Name = DB2INST1.ZIP_CODE ID = 2,96
    | | #Columns = 3
    | | Relation Scan
    | | | Prefetch: Eligible
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Insert Into Sorted Temp Table ID = t1
    | | | #Columns = 3
    | | | #Sort Key Columns = 1
    | | | | Key 1: ZIP_CODE (Ascending)
    | | | Sortheap Allocation Parameters:
    | | | | #Rows = 810143
    | | | | Row Width = 28
    | | | Piped
    | Sorted Temp Table Completion ID = t1
    | Access Temp Table ID = t1
    | | #Columns = 3
    | | Relation Scan
    | | | Prefetch: Eligible
    | Merge Join
    | | Access Table Name = DB2INST1.STORE_LOCATION ID = 2,95
    | | | #Columns = 1
    | | | Index Scan: Name = DB2INST1.STORE_LOC_RET_TYPE ID = 3
    | | | | Index Columns:
    | | | | | 1: RETAILER_TYPE_ID (Ascending)
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | 1: 0
    | | | | | Stop Key: Inclusive Value
    | | | | | | 1: 0
    | | | | Index-Only Access
    | | | | Index Prefetch: None
    | | | | | Insert Into Sorted Temp Table ID = t2
    | | | | | | #Columns = 1
    | | | | | | #Sort Key Columns = 1
    | | | | | | | Key 1: (Ascending)
    | | | | | | Sortheap Allocation Parameters:
    | | | | | | | #Rows = 1478
    | | | | | | | Row Width = 12
    | | | | | | Piped
    | | | Isolation Level: Uncommitted Read
    | | | Lock Intents
    | | | | Table: Intent None
    | | | | Row : None
    | | Sorted Temp Table Completion ID = t2
    | | List Prefetch RID Preparation
    | | Insert Into Sorted Temp Table ID = t3
    | | | #Columns = 10
    | | | #Sort Key Columns = 1
    | | | | Key 1: ZIP_CODE (Ascending)
    | | | Sortheap Allocation Parameters:
    | | | | #Rows = 1478
    | | | | Row Width = 132
    | | | Piped
    | | Access Temp Table ID = t3
    | | | #Columns = 10
    | | | Relation Scan
    | | | | Prefetch: Eligible
    Insert Into Sorted Temp Table ID = t4
    | #Columns = 11
    | #Sort Key Columns = 1
    | | Key 1: (Ascending)
    | Sortheap Allocation Parameters:
    | | #Rows = 1478
    | | Row Width = 136
    | Piped
    Access Temp Table ID = t4
    | #Columns = 11
    | Relation Scan
    | | Prefetch: Eligible
    | Return Data to Application
    | | #Columns = 11
    Return Data Completion

    End of section
    Last edited by iceman; 05-02-03 at 13:23.

  4. #4
    Join Date
    Apr 2003
    Posts
    23
    I guess that explain plan wasn't too helpful since the spaces got jumbled.

    Sorry about that...

  5. #5
    Join Date
    Apr 2003
    Posts
    23
    How about this...
    Attached Thumbnails Attached Thumbnails explain.jpg  

  6. #6
    Join Date
    Apr 2003
    Posts
    23

    Cool Solved

    Well I wimped out on this one and ended up dropping the store_location table and recreating it.

    That flushed out the stats for that table and got our query down to 2,000 timerons, very good performance.

    I am sure there were more elegant ways of solving this problem however.

  7. #7
    Join Date
    Apr 2003
    Posts
    23

    Thumbs up Optimized!

    .
    Attached Thumbnails Attached Thumbnails explain2.jpg  

  8. #8
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    Just to let you know there is a bug in the optimizer. We had IBM build us a special build to help us out. But one of the tricks that we use and it seems to work about 75% of the time is to adjust the nlevels of the index. Of the index has a nlevel of 4 or 5, the chance of that index being used is very little. Change it to a 2 or 3 and see if you have the same problem. The optimizer fix is supposed to be in fixpak 9 or ten, don't remember what they told us.

Posting Permissions

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