summing from multiple tables

[wireless]

We recently added a new database at the company. It has only one
purpose - to hold massive amounts a daily data generated by telephone
calls on a network.

The amount of data was so large (several gigabytes a day) that the guy
who set up the database creates a new table for it each day.

His thinking was that if we only need to query one day's worth of data
then it would be a lot faster to query a table with one day's data
than having to query many days of data in one table.

I see his reasoning. Any comments or alternatives to this scheme
would be appreciated.

Here's the question though...I'm writing a front end for this and was
wondering if the most efficient way to query and sum data across
multiple tables (days) is in the form of the following statement.
Suppose three days of worth of data are wanted:

select sum(ET) from (select sum(vc_elapsed_time) AS ET from
switch2030608 where init_cell_info_cell = 196 union all select
sum(vc_elapsed_time) as ET from switch2030609 where
init_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ET
from switch2030610 where init_cell_info_cell = 196 ) t

In my front end, based on user input, I plan to keep extending this
statement with more union alls. Is this the best way to implement the
goal of this query?

-David

[Erland Sommarskog]

wireless (wi*********@yahoo.com) writes:

The amount of data was so large (several gigabytes a day) that the guy
who set up the database creates a new table for it each day.

His thinking was that if we only need to query one day's worth of data
then it would be a lot faster to query a table with one day's data
than having to query many days of data in one table.
Maybe. With a clustered index on the date, this could be avoided. But
with this large amount of data, this starts to make some sense. Not
the least if you want to remove old data after a week or a month.
Much easier to drop a table, than to delete several GB in one statement.
Here's the question though...I'm writing a front end for this and was
wondering if the most efficient way to query and sum data across
multiple tables (days) is in the form of the following statement.
Suppose three days of worth of data are wanted:

select sum(ET) from (select sum(vc_elapsed_time) AS ET from
switch2030608 where init_cell_info_cell = 196 union all select
sum(vc_elapsed_time) as ET from switch2030609 where
init_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ET
from switch2030610 where init_cell_info_cell = 196 ) t

In my front end, based on user input, I plan to keep extending this
statement with more union alls. Is this the best way to implement the
goal of this query?

You should look into partitioned views. A partitioned view is basically
a view with a UNION ALL clause that combines the tables. You should also
add some constrataints to the table, so that SQL Server knows where the
data is.

The tables would need to have a date column, even if this is redudant
for a single table, and this column would be like:

CREATE TABLE switch_20030911
(date datetime NOT NULL
CONSTRAINT default_20030911 DEFAULT '20030911',
CONSTRAINT check_20030911 CHECK (date = '20030911'),
-- other columns follow here.

The nice thing is if you query the view for a certain date, SQL Server
will only touch the table with that date.

Obviously, the view would need to be redefined each day, but that is
a cheap operation.

Now, I should put in a big fat disclaimer here: I have never used
partitioned views myself. So rather than giving all details here, I
encourage you to check out the topic futher in Books Online.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp