Reports from data spanning mutiple tables

Mi-Jack
Mi-Jack used Ask the Experts™
on
Hi all,

Here's what I have:

Hundreds of tables with identical DDL with only names different,
constructed based on a day-time, so TBL2012041808 is for April 18
2012 8AM data, TBL2012041809 is for 9AM etc.
Each table contains upwards of 300,000 records

What I need to do:

Produce reports from that data. The problem is that if
that is a monthly report, I will be dealing with 720 tables.
Yearly - 8,760 tables.

I am thinking of writing an SP that accepts parameters defining two dates
and returns a resultset from table(s) between those two dates.

Is there even any benefit in writing such an SP in terms of speed?

The real question is:
What would be a better approach to querying such database?

Any help is appreciated,

Thank you,

Gene
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
the best approach for you would probably by to create one big partitioned table and attach all the existing tables into that table, each one as partition

with your current design, you would probably be best off with a stored procedure that generates a massive query with union all from the different tables

Author

Commented:
I don't have that feature - we're using a free version.
I don't have any experience with partitioning tables, either.
Will it speed up data retrieval, or is just more convenient in terms of not having unions?

How difficult is partitioning to set up and configure?

Thank you
it's hard to tell if it will speed up things without actually testing it, but it might because less tables will be in the query
if you don't have that feature, you can't really set it up and configure it... if you had it, it would have been pretty simple
when you create the table you specify the ranges of data you want to go into each partition
from there, when you issue a select statement, according to your where clause, db2 knows which partitions to access

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial