HUGE table?!

[cheapterp]

I am using MS-SQL 2005 DB. One of the tables in the DB, that I inherited, has gotten upto 37000 records. And it will go up by another 50k records soon. Does that make too much data for one table? The table has about 25 columns, most of which contain VARCHAR type data.

Also, since the DB acts as a back-end to the web interface, would about 90k-100k records make the pages load slower? Since the web pages also include a 'search' functionality, every search will have to go through so many records at a time. In other words, what is the performance hit I will take for having so much data together?

Am I being paranoid about the amount of data, or does something need to be done - and if I do, what are my options?

Thanks!

[Ramesh S]

I believe 37000 to 50000 records are too much for a table.

I have seen that some customers are storing more than a million records in a table.

Your database design ensure that normalization techniques to avoid data redundancy.

To improve search performance, you need to create indexes for the columns on which you provided search option.

Please note that some sql server databases used in Microsoft ERP product does not relationships/integrity constraints among the tables. The integrity and relation of the data is validated through front-ent(user interface). But the tables have proper indexes to improve the performance.

[sknake]

If you have a good database design and properly indexed tables then you're good to go. You can safely have millions of records in a single table and it will barely make MSSQL break a sweat ;)

The total length of the table row could be a problem but you would have seen an informational message from MSSQL when creating the table / inserting the data, and this is probably not the case unless you have binary/extremely long characters.

You may consider reviewing FTI/Full-Text Indexing if you are going to be searching entire rows. You will get better performance than matching all of the columns individually.

[sknake]

Here is an example:

IF OBJECT_ID('OneMillion', 'U') IS NOT NULL DROP TABLE OneMillion

GO

Create Table OneMillion
(
  PK int identity(1000, 1) PRIMARY KEY,
  RecordDate DateTime,
  Value varchar(50)
)

GO

Declare @dtStart DateTime
Set @dtStart = GetDate()

SET NOCOUNT ON
BEGIN TRANSACTION 

Declare @m int, @i1 int
Set @m = 1000000 -- 1,000,000
Set @i1 = 1
WHILE @i1 <= @m
BEGIN
  Insert Into OneMillion (RecordDate, Value) Values (GetDate()-(@i1/100), 'abc123')
  Set @i1 = @i1 + 1
END

COMMIT TRANSACTION
SET NOCOUNT OFF

PRINT 'Finished in ' + Cast(DateDiff(second, @dtStart, GetDate()) as varchar) + ' seconds'

Results in:

Finished in 14 seconds

So we generated 1,000,000 records in 14 seconds. Not too shabby, right? Lets try to get some unindexed data brought back.

Select *
From OneMillion
Where RecordDate >= Cast('2000-02-21' as datetime) and RecordDate < Cast('2000-02-22' as datetime)

The query ran in under a second with no indexes on the date columns bringing back a total of 100 records, which was expected because that is the number we used in our division when inserting the records.

[Ramesh S]

Hi Snake,

Sorry it should read as 'I believe 37000 to 50000 records are not too much for a table' in my post.

If you read my post, i actually iterated that an SQL table can have more than a million of table.

Sorry again. It is typo error.

[sknake]

errr... After you said that it does make sense. I thought you were referring to customers with 1mil+ records and I inferred they had bad performance. I'll see if I can have that -rep deleted or i'll make it up over the next 2 days :)

[Ramesh S]

Yes. I should also have told that the customer have better performance with million of records in my post.

Thanks for your reply.

[cheapterp]

Thanks Scott and Ramesh,
I guess the next thing for me to do is look into FTI - I have just started working on this DB, so I am not quite sure if there are any indexes at all in it!

[sknake]

You're welcome

Please mark this thread as solved if we have answered your question and good luck!