> 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 MVP
http://weblogs.sqlteam.com/dang/
"nflacco" <mail.flacco@[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?
|
