asked on

Reports from data spanning mutiple tables

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

momi_sabag

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

Mi-Jack

ASKER

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

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mi-Jack

ASKER

Thank you