Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limitations on record numbers (2000)

    Is it feasible to have a table with 800,000+ records in daily (even hourly) use?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limitations on record numbers (2000)

    Yes it is, but how well it works will depend on how well you have planned for it. The big consideration is that you never want to be in a situation where you have to scan every record in the table to satisfy a query. This means you need to index all fields you will sort by or that you would typically use as selection critieria. If you use bound forms, you don't want to open a form in which the whole table is the recordsource. Same thing with combo/list box rowsources.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limitations on record numbers (2000)

    Thanks for that. Excuse my ignorance but although I understand the logic behind planning tables well I'm a little lost by the idea of avoiding the need to "scan every record in a table to satisfy a query". Surely that's what a query is for; scanning every record?
    Good indices will help me scan them quicker but are you suggesting that I should structure the table such that my 'query1' needs to scan records 1-100,000 (for example) and 'query2' 1-150,000 and perhaps 'query3' scans 125,000-400,000?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limitations on record numbers (2000)

    No, no. That's not what I meant. First of all, a query is not designed to "scan all records". A query is designed "to return all records that meet your specifications". Suppose your table contained a ContractDate field. If I wanted a list of all records with a contractDate on/after 1/1/02. Which would you rather have, a query that examined each of your 800,000 records to see if ContractDate was >= 1/1/2, or a query that could find the first record with a ContractDate >= 1/1/2, and then start returning records?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limitations on record numbers (2000)

    Fair enough but what if the table is a contacts table with a list of 800,000 people and each record is a contact with nothing as simple as a date field to go by. Of course they'll have keys but to find "Peter Smith of "New York, New York" you'll still need to SELECT all WHERE firstname=Peter surname=smith and STATE=NY. Surely, that means scanning all records?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Limitations on record numbers (2000)

    Not if State, Surname and Firstname are an index. It will go straight to NY, then to Smith within NY, then straight to Peter. So even if there were 100 Peter Smiths in NY, thats all that would be scanned and returned by the query (just 100 records, not 800,000 records).
    HTH
    Pat

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limitations on record numbers (2000)

    I'm confused by your question. Do you want to scan all records? My point is that 800,000 records won't necessarily be too much for Access. Whether or not it "fails" is more a question of the skill of the developer. An 800,000 database designed correctly for the situation might work quite acceptably, but a 10,000 record database that isn't designed correctly may provide unacceptable performance.

    As for your last example, Access will use what indexes it has. If you only have an index on STATE, then Access can easily return all the NY records, and then will have to scan each one of those individually to check for PETER SMITH. If you don't have an indexes, Access will scan all 800,000 records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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