Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>