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?

Use UNION ALL instead of UNION ...

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

Cheers
Sathyaram

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

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..

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

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?

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