Recommended SQL Table Size

  • Thread starter Thread starter Jacob
  • Start date Start date
J

Jacob

I'm writing a web application that will collect as many as 200 entries per
day(~20 fields per entry). The data fields will always be the same and so,
if the sky's the limit, I could just keep adding these same entries to one
enormous SQL table-- collecting 200 entries per day, 7000 per month, ~84,000
per year, and on, and on, for the rest of the programs existence. I realize
that this is probably not practical and would cause a serious strain on the
database when modifying entries.

SHOULD I or HOW SHOULD I try and split up this information. Should I create
a new table for every day, month, year? Eventually I hope many people will
be accessing this site, and searching this content, so it should be
efficient.

Any help is appreciated,
Jacob
 
Probably not the right group for this, but 84,000 records is nothing. I've
written apps
that collect more than 84,000 records in an hour or so. Just make sure to
properly
index your table, and you won't have any issues with updating any of the records
or
performing any processes for clean-up or whatever you might need.

If I have my numbers right:
2,480 Terrarium Clients Reporting
13 Records per report
6 Minutes per report

That is 32,240 records (about 10 fields) per 6 minutes or 322,400 per hour. We
ran
our servers for months and months on end before even thinking about clearing out
any
data. To date, the public Terrarium server has only been cleared of data once
and that
was during the first SQL Server attack well over a year ago.
 
Jacob said:
I'm writing a web application that will collect as many as 200 entries per
day(~20 fields per entry). The data fields will always be the same and so,
if the sky's the limit, I could just keep adding these same entries to one
enormous SQL table-- collecting 200 entries per day, 7000 per month, ~84,000
per year, and on, and on, for the rest of the programs existence. I realize
that this is probably not practical and would cause a serious strain on the
database when modifying entries.
Click to expand...

WHOAAA :-)

Right.

Sorry if this sounds sarcastic.

84.000 rows per year.

This means basically that you ahve 840.000 rows in 10 years

and 8.400.000 rows in 100 years.

AND EVEN THEN THIS WOULD NOT BE A LARGE TABLE.

I normally start considering a table "non-trivial" at around 1 million rows
and not small at 10. THis means for you in 100 years.

Never seen a db table with 1.000.000.000 rows? This is possible and without
too many problems.
SHOULD I or HOW SHOULD I try and split up this information. Should I
Click to expand...
create

No, forget it. Get some REAL DATA VOLUME first.
a new table for every day, month, year? Eventually I hope many people will
be accessing this site, and searching this content, so it should be
efficient.
Click to expand...

Nope. Read up some books on how capable your database REALLY is.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
 
That's what I needed to hear. :)

Jacob


Thomas Tomiczek said:
WHOAAA :-)

Right.

Sorry if this sounds sarcastic.

84.000 rows per year.

This means basically that you ahve 840.000 rows in 10 years

and 8.400.000 rows in 100 years.

AND EVEN THEN THIS WOULD NOT BE A LARGE TABLE.

I normally start considering a table "non-trivial" at around 1 million rows
and not small at 10. THis means for you in 100 years.

Never seen a db table with 1.000.000.000 rows? This is possible and without
too many problems.

create

No, forget it. Get some REAL DATA VOLUME first.


Nope. Read up some books on how capable your database REALLY is.

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
Click to expand...
 
You're right, I should have found a different newsgroup. But you're
generous answer was what I needed to here :)

Thanks,
Jacob
 
Back
Top