Results 1 to 6 of 6
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    one single large table

    HI everyone,
    I have a backend dbase that only has 1 table on it that is growing at a rate of about 30,000 records per day or 500,000 records per month.

    I'd like to put this backend dbase on a new computer but am wondering what direction to put the money towards? CPU/ RAM or both / Single core / multi core/ ? 64 bit is better than 32 bit i assume. Anything else perhaps???
    My concern is the potential for the table to become sluggish as it grows larger. I'll be running queries on the front end dbase from 1 work station only (not multi user) over a network.



    Also i'm curious if someone could explain when i run a query from the front end - is the backend sorting the records and only sending the requested records over the network OR is the frontend sorting the records on my local HD (meaning ALL the records are sent the the front end through the network before getting sorted by the query? Makes more sense to me to run the query filter on the backend THEN send a smaller amt of data over the network but it occurred to me the HD holding the backend doesn't have a version of MSAccess on it, It is only holding the data in an access file - so now i'm wondering what's going on exactly.

    thanks in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If your backend is Access, Access has a 2GB size limit you could eventually hit.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is the backend sorting the records and only sending the requested records over the network OR is the frontend sorting the records on my local HD
    really depends on what you are sorting on. Also depends on what your backend is and whether you are using vba functions and whether you have queries which reference other sorted queries. Properly indexed, speed should not be an issue - the issue will be the number of records being returned. Criteria limits the number of records returned, sorting is based on the reduced recordset. Note if the final destination is a report, then the report will ignore sorting, so no point doing it in the recordsource.

    For Access the backend is doing the work - but using your processor and memory. For something like sql server as a backend, the server is using it's own processor and memory

    see you are down from your original 250,000 records/day here https://www.accessforums.net/showthr...851#post463851

    A single table does not sound right, but perhaps it is a simple list with only a few columns - perhaps down from the 20 text columns you mentioned before.

  4. #4
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Quote Originally Posted by Ajax View Post
    really depends on what you are sorting on. Also depends on what your backend is and whether you are using vba functions and whether you have queries which reference other sorted queries. Properly indexed, speed should not be an issue - the issue will be the number of records being returned. Criteria limits the number of records returned, sorting is based on the reduced recordset. Note if the final destination is a report, then the report will ignore sorting, so no point doing it in the recordsource.

    For Access the backend is doing the work - but using your processor and memory. For something like sql server as a backend, the server is using it's own processor and memory

    see you are down from your original 250,000 records/day here https://www.accessforums.net/showthr...851#post463851

    A single table does not sound right, but perhaps it is a simple list with only a few columns - perhaps down from the 20 text columns you mentioned before.
    Thanks Ajax, It's hard to pinpoint the size of this as there a few different things i can do (which might require more records/ day). Having said that what you're saying above is a big help. tks again!

    question though.... if the backend is doing the work (.accdb file) and the backend computer DOESN'T have an MSAccess installed as it's basically just holding the datafile -you're saying the backend is doing the work but using your processor and memory - by that are you meaning the processor and memory of the front end application?

    This leads me to another question.... i'm in the market for a new computer and since it will be holding the front end application should i be looking for a particular configuration? Single Core vs MultiCore CPU, Large amount of RAM, 32bit vs 64 bit? I'm a little out of my league in this regard but it makes sense to me that certain computer configurations will handle large databases such as these better than others. Do you have any thoughts you can share in this regard?

    thanks in advance.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    by that are you meaning the processor and memory of the front end application?
    yes. the ACE db engine is file based. You can open an excel file on a different machine, even if it hasn't got excel installed.

    with regards new machine, although you can install 64bit access it is no faster than 32bit. just get a fast machine - typically Intel i7 processor, 8gb ram, 1tb SSD. Note there is no benefit in additional ram with regards access. Really only of benefit to super power excel users/video editors or having hundreds of apps open at the same time because they all need memory.

    it makes sense to me that certain computer configurations will handle large databases such as these better than others
    I presume you mean 'be faster'. Access only uses a single core and is not multi tasking so beyond the configuration suggested in the previous paragraph, this assumption is wrong. What makes the biggest difference on performance is having an efficient db design and a fast network if the backend is located elsewhere, plus a ssd so programs load quicker. SQL server is server based, so your thoughts are more relevant to that scenario. - just be aware that what is gained on performance will to a certain extent be lost on the network. Servers are a different animal - designed to be connected to by multiple machines, perhaps thousands and carry out multiple tasks simultaneously. I guess if you have the budget, you could user a server as a pc - but access would be no faster

  6. #6
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Quote Originally Posted by Ajax View Post
    yes. the ACE db engine is file based. You can open an excel file on a different machine, even if it hasn't got excel installed.

    with regards new machine, although you can install 64bit access it is no faster than 32bit. just get a fast machine - typically Intel i7 processor, 8gb ram, 1tb SSD. Note there is no benefit in additional ram with regards access. Really only of benefit to super power excel users/video editors or having hundreds of apps open at the same time because they all need memory.

    I presume you mean 'be faster'. Access only uses a single core and is not multi tasking so beyond the configuration suggested in the previous paragraph, this assumption is wrong. What makes the biggest difference on performance is having an efficient db design and a fast network if the backend is located elsewhere, plus a ssd so programs load quicker. SQL server is server based, so your thoughts are more relevant to that scenario. - just be aware that what is gained on performance will to a certain extent be lost on the network. Servers are a different animal - designed to be connected to by multiple machines, perhaps thousands and carry out multiple tasks simultaneously. I guess if you have the budget, you could user a server as a pc - but access would be no faster
    Very helpful - thanks Ajax

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Large table into smaller
    By Thompyt in forum Programming
    Replies: 15
    Last Post: 05-04-2019, 12:55 PM
  2. Replies: 4
    Last Post: 03-08-2018, 03:27 PM
  3. Replies: 3
    Last Post: 08-02-2015, 12:42 AM
  4. Large Table Update
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 01-21-2014, 12:37 AM
  5. Replies: 5
    Last Post: 05-14-2012, 02:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums