Re: Database Statistics... Best way to maintain stats???

or, if you don't want to get into hypercubes, you can

"squash" the data from the first table into a second table

with an aggregate query which adds, at minimum, a RecordCount column
and removes as much unnecessary detail as possible

then, if you need to include both tables in a query, you
can create a union query that simply adds a RecordCount of 1
to the first table so it has the same number of cols

ie: select field1, field2, field3, 1 as recordcount from table1 union
all select field1, field2, field3, recordcount from table2

there is an efficient way to do the transfer

you create a transactional stored procedure that uses a temporary table
to index the primary keys
of the records to be transferred (usually based on date, say 10,000
every 5 minutes or whatever fits)
so it looks roughly like this:

select top 10000 primary key into #temp from table1 order by date
--
insert into table2 (field1, field2, field3, recordcount)
select field1, field2, field3, count(*)
from table1
where primarykey in (select primarykey from #temp)
--
delete
from table1
where primarykey in (select primarykey from #temp)

(you will have to add the transaction and error handling bits yourself)

i used that when getting around 100,000 records per day with heaps
of detail and it compressed nicely about 10 to 1 and my nastiest
olap-style
t-sql queries (12 months of data) took around 15 seconds

and you can easily simulate olaps intermediate calculations
with a single table refreshed every day or on demand

.

Relevant Pages