Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17

    Slow Database


    I created a database to keep track of action items. When I was creating the database everything was fine and it ran at a normal speed. After I split the database and moved it to a shared network drive it has become extremely slow especially the forms with a lot of functions. Any ideas on why this is and how to fix it?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    plenty of reasons

    database not split, frontend/backend
    tables not normalised
    lack of indexing
    using domain functions in queries
    use of lookup/multivalue fields in tables
    inefficient code
    loading too much data (i.e. forms based on a table rather than a query)
    slow network
    front end not compacted

  3. #3
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    It could be the speed of the network drive that is slowing you down, as well. In fact, my money is no that being the culprit. Try running an un-split copy of your original database from within the network drive and see if it slows down.

  4. #4
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    So it looks like I have three levels of "slowness"... It runs fastest when it is unsplit on my desktop. It slowed down when it is unsplit on the shared drive in our network. It is slowest when it is split on the network.

    The database is split thats when I see it acting slower
    I believe my tables are normalised most of them have just one function like contacts or departments. The problem may be i have one table that brings all the other tables together.. like I have multiple lookup values in my "action item table" that pulls from the other simpler tables.
    Im not sure what indexing or domain functions are
    i do have lookup fields in tables as explained above.. How do I get around this?
    again not sure how to compact the front end..

  5. #5
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    That denormalized table should actually speed performance up over a query that issues the same results. Are you using a maketable query to create it? What are you doing to ensure that the table is updated any time that there are changes to the data?

  6. #6
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    Im not sure what a marketable query is but the form that the user enters and changes information in is exactly the same as the table only sorted by some information. So it updates it when the user makes changes

  7. #7
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Quote Originally Posted by Dscalf1 View Post
    Im not sure what a marketable query is but the form that the user enters and changes information in is exactly the same as the table only sorted by some information. So it updates it when the user makes changes
    Maketable....thats what it sounded like when you said you have a table that gathers data from the other tables...that you are querying the tables and writing results in a new table......if in fact it just relates to lookups in other tables, never mind

  8. #8
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    i just needed the other tables in order to use drop down lists in my main table which is action items.. I didnt want the user to go in to the drop down box and edit what it said so i created forms where they can enter information in like contacts and departments and it updates the drop down boxes automatically which I believe is more user friendly if they have never used excel.. Is there any way around this?

  9. #9
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    You're using it for the right reason, I just misunderstood your post above. It makes sense that the standalone on the shared drive is slower than the local drive and faster than the split db, as there would be fewer reads across the network than with the split. I do believe your shared drive connection is what is slowing you down. Aside from migrating the back end to SQL Server, I'd say at least study up on indices and how to optimize them.

  10. #10
    Shadow9449 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Feb 2017
    Posts
    38
    Quote Originally Posted by Dscalf1 View Post
    I created a database to keep track of action items. When I was creating the database everything was fine and it ran at a normal speed. After I split the database and moved it to a shared network drive it has become extremely slow especially the forms with a lot of functions. Any ideas on why this is and how to fix it?
    If the database is split then you want to use a persistent connection. It makes a bit difference.

    Here's more info:

    https://www.fmsinc.com/MicrosoftAcce...dDatabase.html

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is slowest when it is split on the network
    just to be clear - that is front end on your machine, backend on the network? Which is the proper setup.

    Im not sure what indexing or domain functions are
    indexing is a field property. Any field that is regularly used for criteria, filtering, sorting and joins should be indexed. Domain functions are functions like DLookup, DSum

    i do have lookup fields in tables as explained above.. How do I get around this?
    simple. Remove them. You use them in your forms instead (they have probably already been created but form your description, you don't appear to be using forms. Whilst at it, also remove any subdatasheets.

    The compact option can be found as an option in the database tools ribbon, or click on File - you'll see it there as well.

  12. #12
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    I believe that the problem has to do with the overall "structure" of this program(app, thing, whatever)...

    If the back-end exists on the network and the front-end runs any code that you need to display/manipulate your data the following problem occurs. The transfer of large amounts of data is slow on your network, which is unfortunate. I have a very similar problem. While your code may be sufficiently fast (locally) the code must wait on data to be delivered via the network before it can do any of the intended processes you've written.

    A server on the other hand would have direct access to the back-end and do all of the data-crunching locally. I could then deliver the results to whatever client is in need at that moment. The guidance I received on this process was "Stop using Access."

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The guidance I received on this process was "Stop using Access."
    The problem is not with using access, it is how access is being used. Agreed, using sql server means you can push backend processing back on the server. However on more than one occasion, I have had clients move from sql server to an access backend because sql server was too slow (under resourced for all the activities it was required to do) and the big guns were reluctant to invest more money. And they certainly don't have the money to invest in a new front end based on html/whatever to be used by just 5 people out of an organisation of 1000's.

    Simplistically, if you apply the same principles as used by websites for the population of forms and reports, performance will be just as good or not far short. A slow network is a slow network. Like for like any system will have the same problem if the network is slow.

  14. #14
    ohmydatabase's Avatar
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    79
    sql server was too slow (under resourced for all the activities it was required to do)
    This is really what I was worried about happening if I went down that road. Plus, I know nothing about web-based apps. So I've still been using Access.

    If the database is split then you want to use a persistent connection. It makes a bit difference.
    I read the article provided by @Shadow9449 and I am really hoping that maintaining an open connection with the backend (at all times) would help with the speed issues. I am a little confused about that actually process though. When I declare some variable (say db) as a DAO.Database and set it the current database, is that point the instant where the connection is established?

    Code:
     
    Dim db As DAO.Database
    Set db = CurrentDb()
    Up until this point, I have been Dim-ing and setting the DAO.Database in the Subs/Forms that I need. Using the OpenDatabase() Method, will all of these "Dim-ing & Setting" commands be redundant? Would if it be better to remove all of my declarations/initializations of db -> CurrentDb(), and do it once the database opens. (Via the AutoExec Macro?) I guess I am unsure if the act of opening the database is the same as setting the variable (or pointer?) db to that CurrentDb().. Also, is there some sort of "Anti-AutoExec" that automatically runs when the application closes? I'd like to be able to close the connection to the database.

    The lock file also confuses me. Once the lock file is made, is that current 'version' of the backend 'locked'? Are updates to the backend going to be properly reflected if the lock-file is present? Would I need to introduce a lot of 'Refresh' commands throughout my Subs to ensure I am getting the most current data? Does this introduce any new problems? (Ex: I have some users that never close their local, front-ends. The lock file would exist forever, right?)


    Sorry that was so much, this has been a hot-button issue and I'm really excited to increase speed of data transfer!

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think that is one for Shadow to answer....

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Slow Database
    By shaun_za in forum Access
    Replies: 2
    Last Post: 09-21-2015, 06:23 AM
  2. Database Running Slow
    By data808 in forum Access
    Replies: 2
    Last Post: 08-12-2014, 01:06 AM
  3. split database queries slow fyi
    By survivo01 in forum Access
    Replies: 1
    Last Post: 08-24-2013, 01:08 AM
  4. Slow Database
    By cadsvc in forum Database Design
    Replies: 3
    Last Post: 05-31-2011, 09:48 AM
  5. Slow Database Response
    By Nixx1401 in forum Access
    Replies: 3
    Last Post: 02-25-2010, 11:09 AM

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