How many records is too many records in Access for ASP?

[jasonsalas]

Hi everyone,

This query grew out of a previous thread. I was wondering if anyone knows how many records in a recordset is excessive for an online Access 2000 database called from a public Web site.

At the moment, I'm managing a DB that has about 1,600 records in a single table, from which a stored procedure generates a recordset of the same number of records, but I've got varying opinions from DBAs and Web devs on the topic.

Some say simple database queries (i.e., not in the vein of complex search scripts looking for matches based on searchstrings with a " LIKE '%' " SQL statement) can go as high as several tens of thousands of records without compromising speed. Others say that to preserve the interity of performance, smaller tables is recommendable, breaking down the data into a couple hundred.

Any suggestions?

 

[chiph]

I wouldn't measure performance in terms of records retrieved or scanned, but in terms of how many seconds does it take the average user to get their page shown.

Chip H.

 

[NightWatcher]

Hi,
the best way is to use only one table, and the maximum amount of records can grow to the tens'o'thousands as mentioned with ease. The other approach, having several smaller tables will actually degrade preformance as it has to make several calls and jumps to get all the data.
I also advice, to the possibility of buffer overflows, and timeouts when queryng tables with a large amount records, you might have to tweak the server timmings, for an error free operation, and turn off 'response.buffer' as this takes a little longer to compute but it's nicer on the client.

Hope this helps.