Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unanswered: How to retrieve value from sub-select/method first in filtered query

    Hey there,

    I'm currently building a query in DB2 that ultimately will retrieve a bunch of data limited to a date range. The year is split into 12 periods and I always want the data limited to the previous period to the current date.

    If I write the query so that it can dynamically determine this date range it seems to execute this portion of the code after it has retrieved all the data, filtering it afterwards. This kills the performance of the query.

    Whereas if I take the same query and just hard code the date range then the query takes a fraction of the time to complete.

    What I'd like to know, is there any way to tell DB2 to complete a sub select or method called within another query first and then proceed with the main query? Or if anyone can suggest a different approach to getting the same results I'd really appreciate your help.



    TIA,

    Brian

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think anyone here will be able to suggest anything meaningful without your describing the problem in more detail. At the very minimum you will need to provide the query, table and index definitions, and the query execution plans in both cases.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    I don't think the table structure or indexing has anything to do with it. It's more how DB2 handles the query. Below are two examples of basically the same query.

    The first uses a method to derive the date I need. Using this method blows the overall cost through the roof and will make my query run for hours. The method itself is not costly on it's own.

    select *
    from mytable t
    where t.period_ending_date = get_period_date('PREV', CURRENT DATE, 'END')


    Where as in this query I’ve hard coded the date to the same value the method would return and the cost of the query and the time it takes to run is a fraction of the previous query.

    select *
    from mytable t
    where t.period_ending_date = '7/29/2007'


    From what I've been told the first method takes so long because it is retrieving all the data from mytable and then filtering it afterwards. While the second query is only bringing back the data I need based on the date value.

    Any help would be greatly appreciated.

    Brian

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since your have performance problems, the only way to help you is if you provide all the requested information. In particular, you also have to tell us:
    • how the GET_PERIOD_DATE function is defined,
    • how DB2 is configured (buffer pools etc.),
    • if and when you collected statistics on the table,
    • what the exact execution plan is for the queries.


    The thing is that the DB2 optimizer tries to take all relevant system and DB2 configurations as well as statistics etc. into account to come up with the (hopefully) best access plan.

    My (wild) guess is that your function is created NOT DETERMINISTIC and/or with EXTERNAL ACTION. Thus, DB2 must call the function for each row in the table, resulting in a slow table scan. The 2nd query w/o function call can be much faster if there is an index on PERIOD_ENDING_DATE, for example.

    p.s: The whole idea of an RDBMS is that you do not tell the system how to retrieve the data - you only tell it which data it should return and the system does the rest.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Try putting the method into a non-correlated sub-query. That should retrieve the date once and you might get a better access path.

    Code:
    select *
    from mytable t
    where t.period_ending_date = (
      select get_period_date('PREV', CURRENT DATE, 'END')
        from sysibm.sysdummy1 )

  6. #6
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Here's another option that might provide some relief. Create a declared temp table and insert one row with the date, and then join to the temp table for the date.

    Code:
    declare global temporary table per_end_dt_tbl
      (per_end_dt date)
      on commit preserve rows
    
    insert into session.per_end_dt_tbl (per_end_dt)
      select  get_period_date('PREV', CURRENT DATE, 'END')
        from  sysibm.sysdummy1
    
    select  *
      from  mytable t
     inner  join session.per_end_dt_tbl p
     where  t.period_ending_date = p.per_end_dt

Posting Permissions

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