Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how big can it get ?

    What is the viable limit for an Access table ? I need to provide a solution for our Network Manager who has to analyse telephone billing data. this will result in approximately 24000 [img]/w3timages/icons/yikes.gif[/img] rows of data (many calls per physical telephone line). My current feeling is that i would be better off using a SQL database with Access as the front end. At worst i need to keep 4 quarters data accessible for reporting purposes but will need to produce reports year on year for each telephone line that we have. So a solution that leaves all data (within reason) available would be best.

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    Hi

    I worked in a call centre where i imported data directly from 4 Unison diallers for analysis. Each of the four tables contained around 15,000 records, and each day i appended them to a "master" table just for backup. This continued for each quarter.

    I had no problems, except for speed.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    Speed is my main concern, where you just saving the data or activly using it? If my user gets to run a report that will take an epoch then he'll not bother using it.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    24000 rows is no problem for access.
    Make sure that when you want to retrieve your data, your selections run on an index and not on the main table, then performance should be no issue at all.

  5. #5
    dmatherton
    Guest

    Re: how big can it get ?

    I guess this is obvious, so apologies if I'm wasting your time.

    Certainly look closely at your indexing for the table concerned and query against the index in order to get quick results.

    You could also take a look at your archiving method - hiving off all out-of-date records to a different database and then linking the table back to your original front-end, would let you run the year-on-year stuff, without burdening the users with a bloated database.

    D.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    I'm happy for 24000 rows but this is per quarter ie one year (the minimum period for reporting) will involve 96000 rows. will this be too much?
    Also, how best index the table for the report ? there are only half a dozen fields. obviously the phone number is indexed, do i need others ?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    OK
    Let's put it on 100000 records.
    It is a lot, but it can be done.
    First make sure you have NUMERIC fields you select on. Make als sure that all the fields in the WHERE clauses of your queries are numeric and indexed.
    Use the TOP quealifier in a query whenever possible.
    Design you database structure well, ensure every table has a unique numeric key.
    Prevent a table scan, that takes a lot of time! Use referential integrity!

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    I just thought I'd throw my $0.10 in....

    Remember that Access can handle individual tables as large as 1GB. That's a LOT of data!!![img]/w3timages/icons/yikes.gif[/img]

    But I agree speed is certainly not taken into account there...[img]/w3timages/icons/doh.gif[/img]

    [img]/w3timages/icons/thinks.gif[/img]
    -MarkJ-

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Wilmington, NC
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big - "Functional Limit" of Jet Database??

    I hope this isn't too far off-topic for this thread, but Fr. Jack's wondering re. moving to SQL triggered this...
    "(The Jet Database) is physically limited to a little more than 2GB of storage; its practical limit is only a few megabytes" Laura Stewart,'Platinum Edition Using MS Office 2000' (Que), p.974 What the heck is a "practical limit"???

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

    Re: how big -

    I'm not familiar with the book, but if it says that, I wouldn't recommend it because she doesn't know much about Access. That's one of the problems with general "Office" books anyhow.

    The 2Gb limit is new to Access 2000. Previous versions were stuck at 1Gb, but note that either is a serious performance issue, so don't go by that.

    Access's "practical limits" are determined by the machine it's running on, the network (if any), the number of simultaneous users, and the design of the application. I've had databases in the 500Mb range that handled up to 6 simultaneous users fairly well for data entry, but I had to be very careful about the design of the user interface. Reporting and data retrieval are another issue and require their own design considerations.

    Oh, I forgot. The largest single table in that database had around 70,000 records, with several others in the 30 to 40 thousand range.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    Just another response to the question. About 2 years ago I wanted to see about the speed in Access 97 and large data sets. This is very artificial but I made one table with a variety of fields (int, string, date ...) and generated over 1000000 records by code, filling with random entries. I made a query, filtering on an indexed field. It would find all records in 0.5-1 second. Note this was 2 years ago, think of the hardware one has now.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    Hi Andy,

    You gave the clue, you filtered on an indexed field.
    Imagine the response time if you selected on a field that has no index....
    If the number of records in a table is growing, indexes can make the difference between hit enter, get coffee and hit enter, see results!

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    Sooooooo, to sum up. Yes. !?
    Would i be safe having the table grow for one year (approx 100000 rows, 8 fields) and have the telephone number, cost , duration and time as indexes? Should I index a field where it is required for calculations or only where it is used for comparisons (WHERE clause)? After a year i could remove the table from active service and replace it for the new year ?
    J

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    An index is used when you are searching for data, so you should put an index on the fields that are used in a WHERE clause.
    Once you have found the record, you can access it through its keyfields, which have an index by default.
    It is not a good idea just putting indexes on every field; applying indexes speed up searches, however, they slow down inserts, updates and deletes because those actions have to be done on the indexes also (access does this automatically, so you don't have to bother).

    Carefully consider whether you need the search facility (and the index).

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how big can it get ?

    This is the point where the database developers earn their keep or the wrath of the users!

Posting Permissions

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