System Robustness

Topic

New Reply

I am working with a potentially large set of data (numbers in the millions). Does anyone have any ideas on the Access’ ability to process record amounts, given standard field lengths? Thanks for the help.

Reply | Quote

Replies

I believe than when your databsae approaches 1 gig, you will be reaching the limits of Access. I am not sure how many records this is.

Reply | Quote

Millions of records would drive the engine to its knees even if they don’t exceed the size limitation. With this volume of data, go for a SQL Server back end.

Reply | Quote

What are you referring to, when you say “even if THEY don’t exceeed the size limitation” And when you say millions, do you mean 2M, 3M, etc??????? Thanks

Reply | Quote

For the hard limits in Access 97 (i.e. those that you can’t exceed), see limits, program in the built-in help. I assume there’s something similar in the Access 2000 help (I have both, but the way I’m set up requires me to re-boot to switch back and forth, so I’ll leave you to look ).

However, those limits just tell you what is guaranteed not to work. As Charlotte said, when you get into millions of records (I’d be dubious of even tens of thousands), you really want something more robust like SQL Server, Oracle, etc. How many millions (or tens of thousands) can’t be answered; it depends on your record sizes, database complexity, tolerance for sssslllloooowwwwnnnneeeesssssss, number of simultaneous users, etc. etc. etc.

Reply | Quote

Just so I understand how Access and SQL Server differ.

Access is essentially a file server and brings all records in a table across the network (when queried) while SQL Server is a database server and will bring across the network only what the query asked for? Is that correct?

When you have a slow database, due to tables with many fields (say over 25) and/or tables with many records (hundreds of thousands), what other advantages does SQL Server have over Access?

TIA

Dave

Reply | Quote

Yes, Access/Jet is a file server, although it may not be completely correct to say that it returns all records in a table when queried, unless you include the fact that it returns all records in all tables that are involved in a query.

SQL Server is a true database server, but if you use Access as its front end, you still have to be careful or your query will be processed by Jet rather than SQL Server, and you won’t be any further ahead.

The biggest advantage of any database server over a file server is that you can build a 3-tier (or n-tier) client/server application with the business logic living in a tier separate from the front end. You can’t really do that with an Access mdb, although you can do it with an Access 2000 ADP against a SQL Server back end. With the business logic living elsewhere, you don’t have to handle all the validations in the interface or at the table level.

This is not to say that you can’t make SQL Server behave as inefficiently as any file server. You can, and it doesn’t take much effort. Just switching to SQL Server won’t cure bad database design or reduce network traffic. That takes development know how and skill.

Reply | Quote

In order to conserve space on our AS/400 system, we recently pulled 4 years of accounts receivable history into an Access (2000) database. Total records ~ 3.5 to 4 million, 20+ fields, indexed on 4 of the fields. After all the conversion work was done, the database was burned into a CD-ROM, taking up about 450MB. A separate database was created to hold all the queries and reports, linking back to the AR History database – which will allow us to insert different AR History CD’s with other years’ data without having to revise our data retrieval DB.

Now for performance:

The whole system was set up on a 400Mhz Celeron PC, 64MB RAM, Win98A. While creating the history database, updates were understandably slow due to the indexes. Compacting the database was a 20 minute operation (so the “compact on close” option had to be disabled). Accessing the data was extremely fast, since each possible search criteria was indexed. Report data could be printed in its entirety, as was the only option previously, but now we could also print specified portions of the report, or just view the information on-screen.

Splitting the DB into the two components gave us more desirable performance. Accessing the history DB through the link instead of from within the same database was marginally slower, but still quite fast. This also would allow us to load the 150K query & report DB on the user’s PC, set to access the 450 MB history DB remotely through the network or more directly through their own PC’s CD-ROM.

As an extra benefit, we were also able to attain much more flexibility and speed in accessing data than was previously possible when this information was being accessed from the AS/400. The only way to access the information online was through running an interactive query (as opposed to sending the query out for batch processing). Any request for information had the potential to drag the whole system to a near-standstill. Overcoming this problem was another consideration when we were converting to an Access-based system.

Standard “Diet Ad” disclaimer — individual results may vary.

Reply | Quote