Recommended SQL Table Size

[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

[Justin Rogers]

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.

--
Justin Rogers
DigiTec Web Consultants, LLC.

"Jacob" <ja**********@REMOVETHIShotmail.com> wrote in message
news:Fr4Ob.5843$A74.2285@fed1read02...

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

[Thomas Tomiczek [MVP]]

"Jacob" <ja**********@REMOVETHIShotmail.com> wrote in message
news:Fr4Ob.5843$A74.2285@fed1read02...

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.
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 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.

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

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)

[Aaron]

How does you index a database table?
is there any reference site on this?

"Justin Rogers" <Ju****@games4dotnet.com> wrote in message
news:e6**************@TK2MSFTNGP11.phx.gbl...

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.

--
Justin Rogers
DigiTec Web Consultants, LLC.

"Jacob" <ja**********@REMOVETHIShotmail.com> wrote in message
news:Fr4Ob.5843$A74.2285@fed1read02...

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

[Jacob]

That's what I needed to hear. :)

Jacob
"Thomas Tomiczek [MVP]" <t.********@thona-consulting.com> wrote in message
news:eY****************@TK2MSFTNGP12.phx.gbl...

"Jacob" <ja**********@REMOVETHIShotmail.com> wrote in message
news:Fr4Ob.5843$A74.2285@fed1read02...

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.

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

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.

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

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)

[Jacob]

You're right, I should have found a different newsgroup. But you're
generous answer was what I needed to here :)

Thanks,
Jacob
"Justin Rogers" <Ju****@games4dotnet.com> wrote in message
news:e6**************@TK2MSFTNGP11.phx.gbl...

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.

--
Justin Rogers
DigiTec Web Consultants, LLC.

"Jacob" <ja**********@REMOVETHIShotmail.com> wrote in message
news:Fr4Ob.5843$A74.2285@fed1read02...

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