On Jul 3, 8:14 pm, "Dan Guzman" <guzma...@[EMAIL PROTECTED]
>
wrote:
> > Another friend of mine suggested using file partioning ( though he
> > uses MSSQL ), so is that another option?
>
> Partitioning is good for managing very large tables because you can
rebuild
> individual partition indexes without touching the entire table. This
> reduces rebuild time and intermediate space requirements. Be aware that
the
> partitioning feature is available only in Enterprise and Developer
editions.
>
> With a good indexing strategy, response time should ideally be
pro****tional
> to the amount of data retrieved (barring cached data) regardless of
whether
> or not partitioning is used. Partitioning by date can facilitate
certain
> processes, like incremental data loads and purge/archival as well as
certain
> types of queries. However, with or without partitioning, indexing is
the
> key from a a performance perspective.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> "nflacco" <mail.fla...@[EMAIL PROTECTED]
> wrote in message
>
>
news:9ee68f4f-04e9-4039-8f8f-b09af9c4a8b9@[EMAIL PROTECTED]
>
> > I'm tinkering around with a data collection system, and have come up
> > with a very hackish way to store my data- for reference, I'm
> > anticipating collecting at least 100 million different dataId
> > whatevers per year, possibly much more.
>
> > ---366 data tables ( one for each day of the year ), each row being
> > assigned a unique DataId ( unique across all 366 tables too )
> > ---100 data_map tables, table 0 having all DataIds ending in 00, table
> > 99 having all DataIds ending in 99 and so on.
>
> > This is mostly because a friend of mine who works with mySQL said it
> > is very slow to index large tables, even if you work with mostly
> > integers.
> > However, I've read mysql can handle millions of rows no problem, so it
> > seems my basic design is overly complicated and will lead to tons of
> > slowdowns thanks to all the joins.
> > Another friend of mine suggested using file partioning ( though he
> > uses MSSQL ), so is that another option?
>
> > Any advice?
The re-indexing is what worries me. I'll be constantly adding new data
to main table ( formerly the 366 day tables ) if we follow the not use
too many tables scheme, as well as the processed-data tables.
(
|
