Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Question Unanswered: Union in 2 partition table very slow

    Hi,

    I am using DB2 UDB 7.2 in Win 2K server, when i perform a UNION between 2 partition table :

    Select *
    from table2003

    Union

    Select *
    from table2004

    The objective i union both year table is to do a time comparison between this 2 year data to calculate some value like price change for the past 1 month, 3 month, 6 month and 1 year.

    To run this query , i take very long time, the row count for each table is about 1 milion. Is there any performance tuning trick and way for me to speed up this query?
    Hello

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Use UNION ALL instead of UNION ...

    Have you considered using UNION ALL Views ... That may make your queries more efficient and scalable

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2005
    Posts
    191
    While using UNION ALL will speed up the query, I wonder if it will speed up the overall process. I presume you'll end up doing something like accumulating data over date ranges in a program and comparing as you go. That means there's probably an "order by date_col" in mix somewhere.

    Why not have two cursors, one for table2003 and one for table2004? The program sucks data off one for the appropriate date range, then off the other, generates the comparisions, then goes back to sucking in data.

    If you are trying to do it all in SQL, try summarising data in nested/common table expressions so that only minimal data is actually processed in the union.

    How often is this going to be run. Once? If so, maybe the slow response doesn't matter. If often, maybe you need an index on the appropriate column(s).

    James Campbell

  4. #4
    Join Date
    Mar 2004
    Posts
    24
    Hi there,

    i had try using UNION ALL and order by some key, but it seems doesn't help much.

    This Union job need to be done daily, i had try to populate this 2 table in a temp table , but even took me 2 hours and it still running!

    if i try to index both the table, what should i do?

    the table key is :

    D_trade, I_Market, I_Industry, I_Sector, I_Subsector

    thanks..
    Hello

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    So the query doesn't have an order by clause? What happens if there were no trades exactly one month ago? Without that order by clause (and I did raise the question) you must be doing some sophisticated processing.

    I wonder what.

    James Campbell

  6. #6
    Join Date
    Mar 2004
    Posts
    24
    I did do the order by like the following:

    (SELECT *
    FROM SIMS.$$CURR_YEAR_SECQUO
    WHERE
    SIMS.$$CURR_YEAR_SECQUO.I_BOARD IN ('M', 'F')
    AND
    SIMS.$$CURR_YEAR_SECQUO.I_TRADING_METHOD = 'AM'
    AND
    SIMS.$$CURR_YEAR_SECQUO.I_TRADING_TYPE = 'R'
    ORDER BY
    SIMS.$$CURR_YEAR_SECQUO.D_TRADE)

    UNION ALL

    (SELECT *
    FROM SIMS.$$PREV_YEAR_SECQUO
    WHERE
    SIMS.$$PREV_YEAR_SECQUO.I_BOARD IN ('M', 'F')
    AND
    SIMS.$$PREV_YEAR_SECQUO.I_TRADING_METHOD = 'AM'
    AND
    SIMS.$$PREV_YEAR_SECQUO.I_TRADING_TYPE = 'R'
    ORDER BY
    SIMS.$$PREV_YEAR_SECQUO.D_TRADE)

    But the running time still the same as i take out UNION ALL and ORDER By clause.

    There is impossible there a no trade (record) for everymonth as this is a stock exchange data in the whole country.

    as i mentioned before, i have to take full 2 year data as i need to compare 2 years value like the price change for the past 1 month, 3 month, 6 month and 1 year ago.


    Did create index on this 2 table help in the performance bottleneck? if yes, how i can do tat?
    Hello

  7. #7
    Join Date
    Jan 2005
    Posts
    191
    I'm just curious how you can be certain that there is always at least one trade in every month. Is there a stock exchange rule that requires this - even if, for some reason, trading is suspended? (Here in Australia this assumption would definitely be incorrect, I just don't know about your part of the world.)

    Personally, I would calculate the starting and ending dates of each month and feed them into the selects:

    select * from SIMS.$$CURR_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-1-month-ago and :hv-end-of-1-month-ago
    union
    select * from SIMS.$$CURR_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-3-month-ago and :hv-end-of-3-month-ago
    union
    select * from SIMS.$$CURR_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-6-month-ago and :hv-end-of-6-month-ago
    union
    select * from SIMS.$$PREV_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-1-month-ago and :hv-end-of-1-month-ago
    union
    select * from SIMS.$$PREV_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-3-month-ago and :hv-end-of-3-month-ago
    union
    select * from SIMS.$$PREV_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-6-month-ago and :hv-end-of-6-month-ago
    union
    select * from SIMS.$$PREV_YEAR_SECQUO
    where <current predicates>
    and D_TRADE between :hv-start-of-12-month-ago and :hv-end-of-12-month-ago
    ORDER BY D_TRADE

    This allows much filtering since you'll only fetch data for 4 months, not at least 12, and up to 24, as you are currently doing.

    James Campbell

Posting Permissions

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