Results 1 to 12 of 12
  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77

    Creating a local but updatable copy of a large database

    I've a problem with a rather large database I've gained access to. Using Access, of course.

    It is very large, a parent table with some 12.000 records and a child table with data related to the parent table numbering around 400.000 records (and that's just part of it, but the worst part).



    This puts a drain on my stamina when doing queries. As soon as they become the least bit complicated, Access grinds to a halt for several minutes, probably because of doing calculations with that large number of records.

    I'm not inclined to change my preferred database access tool, which is Access. Instread I wonder if this might be handled in a more practical manner.

    I got the idea that perhaps I could simply download a local copy of the data, and query that. If I cut out the need for Access to call the server where the data is stored I gather a lot of time may be saved.

    As it is, I've made a call to an ODBC database, with an updating chain of data. but it seems I can't ask it to store data locally and only update when asked.
    I can make a local copy of the tables from the ODBC in Access, but I can't seem to find a way to make such a local copy update any of its data, rendering it fast but useless because I can't update.

    I would like to store the data locally for fast access but retain the ability to update the data in such a local table at will.

    Is there a way to do this?

    Thank you in advance.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Whilst I understand that queries based on local tables would be faster, I don't understand why making local copies would make them read only.
    How exactly are you converting them?
    Do the local tables still have PK fields?

    I strongly advise against trying to update the data locally if you intend to upload the new data to the live database afterwards.
    Doing that creates a significant risk of overwriting data by mistake and isn't good practice.

    Much better to fix the causes of your queries running slowly.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I agree with ridders, risks are too great - unless of course you are the only user to make changes, in which case keep the db local and copy the backend back to the server as required

    with regards speed, this is often caused by lack of or inappropriate indexing. And perhaps your query design skills need improving.

    Some basic tips.

    Ensure all fields regularly used for joins and/or criteria are indexed, except those with a limited range of values such a booleans
    where these fields contain large amounts of null values, modify the index to exclude nulls (see the index option on the query design ribbon)
    use PK's or numeric values for joins/indexes wherever possible - text indexing will be slower.

    for query design
    avoid domain functions, avoid subqueries - use a virtual table/another query instead
    avoid grouping wherever possible, should only be required when summing/counting etc. If you are grouping otherwise, it implies a design fault in your db or the data. And if grouping, ensure criteria are applied to the original field, not the grouped field.
    avoid using the initial * in like criteria - using it prevents the db from using indexing. Consider modifying table to separate a field into separate fields - e.g. better to store first name, last name rather than full name if you are searching on last name
    look at the calculations. complex nested iif statements suggests it is time to look to create a 'rules' table of some sort. Constant checking for null will also slow things down.
    look at your table design and relationships - perhaps they are not normalised - working with denormalised data will be slower.
    when working across a network, see what can be done to minimise network traffic - get your criteria in as soon as possible to reduce the number of rows and only bring through the columns required.

    A client had a query which was taking over 10 hours to run - to the extent they would run it overnight. By the time I had fixed it in line with the above, it took 20 minutes.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    An excellent summary by ajax on how to optimise queries
    My best improvement in query speed was a reduction from 35 minutes to 7 seconds.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    ...
    look at the calculations. complex nested iif statements suggests it is time to look to create a 'rules' table of some sort. Constant checking for null will also slow things down.
    ...
    Rules table?

  6. #6
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Well there is a lot to work with, it seems.

    Perhaps also a need for clarification: I don't need to copy data TO the back-end, this is a database that supports multiple users via a GUI and I don't want to mess with data, ONLY to read them.

    It's just that reading them seems to take so much time.

    It seems there is good advice for optimization. Thank you for that, I'll look into it.

    If there would also be any advice for the original question, I'd be grateful too.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Can you answer my questions from post 2
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by ridders52 View Post
    Whilst I understand that queries based on local tables would be faster, I don't understand why making local copies would make them read only.
    How exactly are you converting them?
    I've tried three ways;
    One is to import the data from ODBC. This creates a local copy, but I cannot seem to update the data in the resulting table.
    Another way is to make a table-generating query where I query the data from the database and create a local table from that. Also, I cannot make this table update its data.
    The third way is the connection I have now, which is an updating chain to the ODBC database, but this generates a lot of traffic which is what I believe slows me down (at least most of it).
    Quote Originally Posted by ridders52 View Post
    Do the local tables still have PK fields?
    Quote Originally Posted by ridders52 View Post
    I don't know, but I can probavly make certain they have.
    I strongly advise against trying to update the data locally if you intend to upload the new data to the live database afterwards.
    I don't
    Quote Originally Posted by ridders52 View Post

    Doing that creates a significant risk of overwriting data by mistake and isn't good practice.

    Much better to fix the causes of your queries running slowly.
    I'll look into query design as well, but I still believe the huge number of records in the database causes a lot of the slowdown.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It is essential that the data tables retain their primary key fields.
    With no primary key fields, data sources used in queries & forms based on several tables become read only

    I never use ODBC to link tables so can't advise in that respect
    However, to convert and retain PK fields, do ONE of the following:

    1. Select all tables you want to convert, right click & run ‘Convert to local table’ - that's it! Couldn't be easier
    Make sure no local tables are selected or the option will be disabled

    2. Loop through all linked tables using this code:

    Code:
    Public Sub Link2Local(ByVal sTable As String)
    
    On Error GoTo Err_Handler
    
    
        Dim sTmpTable As String
    
    
        sTmpTable = "mytmptable"
    
    
        'Make tmp table
        DoCmd.RunSQL "select * into mytmptable from [" & sTable & "]"   'Wrapped in [] just in case of spaces / special characters in name
    
    
        'Delete original table
        DoCmd.DeleteObject acTable, sTable
    
    
        'Rename tmp to oldtable
        DoCmd.Rename sTable, acTable, sTmpTable
        
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Handler
    
    
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by ridders52 View Post
    It is essential that the data tables retain their primary key fields.
    With no primary key fields, data sources used in queries & forms based on several tables become read only

    I never use ODBC to link tables so can't advise in that respect
    However, to convert and retain PK fields, do ONE of the following:

    1. Select all tables you want to convert, right click & run ‘Convert to local table’ - that's it! Couldn't be easier
    Make sure no local tables are selected or the option will be disabled

    2. Loop through all linked tables using this code:

    Code:
    
    
    Thank you, though I opted for a table-generating query that I may then have a macro run for update.
    My predicament is this, the database design is fundamentally flawed in ways I have not yet been able to count and so there is a LOT of redundant columns in the tables. By creating a table-making query I can have the luxury of only keeping those columns that contain relevant data.
    The solution you did was marginally faster, but with a table-making query (and a macro) things go rather smooth anyway.
    So I might have solved it myself. But thanks anyway

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    My best improvement in query speed was a reduction from 35 minutes to 7 seconds.
    smaller query but better percentage improvement!

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    LOL. Your query is bigger than my query....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Taking a large Excel workbook and creating a database
    By desertratz in forum Database Design
    Replies: 3
    Last Post: 01-16-2016, 03:11 PM
  2. Replies: 2
    Last Post: 06-08-2015, 02:56 PM
  3. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 PM
  4. Creating an updatable query recordset using Access 2010
    By Bill McCoy in forum Programming
    Replies: 1
    Last Post: 04-17-2012, 09:36 AM
  5. Creating a local network Database
    By blukit01 in forum SQL Server
    Replies: 1
    Last Post: 03-15-2012, 01:20 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