Results 1 to 8 of 8
  1. #1
    drjabear
    Guest

    System Robustness

    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.

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Location
    London, Gtr London, England
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Robustness

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: System Robustness

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

  4. #4
    drjabear
    Guest

    Re: System Robustness

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Robustness

    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 [img]/w3timages/icons/grin.gif[/img]).

    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.

  6. #6
    DaveShmave
    Guest

    Re: System Robustness

    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

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: System Robustness

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

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: System Robustness

    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. [img]/w3timages/icons/nuts.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •