Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936

    Speeding things up

    I am converting a table which contains almost 6 million records. Every time I touch anything it takes hours and I would like to find out if anyone has tips on how to make at least the updating part of it go quicker. It is hard to experiment with different ways of accomplishing this when everything takes so long.

    • Around 250,000 records are being updated - two fields, both of which are indexed. Would it help much to remove those indexes?
    • It is in version 2003 (mdb) while I am using 2010. In one attempt I converted it to 2010 to see if that would help but converting it back took an entire day (in fact I think I gave up on it)
    • I inserted a make-table query before the update query which creates a table of only those records which need to be updated, not sure if this helps but it does have the advantage of no longer having to use linked tables in the update query
    • The update query had been running for 6 hours when I left for the day!
    • This is happening in three separate conversions over a period of weeks so this is only about a third of it.


    Would reading/updating the records in VBA make it any faster?



    PS Please don't answer questions that haven't been asked here - I have already been thru it all with the users! Any changes I make have to be done in secret and put back the way they were after conversion.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you are using THAT many records, its time to step up to SQL server.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Using VBA is not going to make things faster. However, you can use VBA to assign SQL to a Query Object within the QueryDefs collection. Using a Query Object will be the best performer.

    In addition, avoid getting a table dump by applying WHERE statements and avoiding GROUP BY and HAVING. So use VBA to build an SQL statement that includes the values of your variables and a WHERE clause. Assign this SQL to a temp SQL Object. Then, use that as a subquery to another query in order to avoid using GROUP ON and HAVING.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you mean by "using a query object" as opposed to running a query from the db window.

    No where's or having's or group's, only linking by key fields. Very simple and straightforward thankfully.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you tell us the process and what is getting updated (fields, values, etc.)? Also you said you are converting the table? What does that mean?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    6m records is top end but in itself does not mean it is necessary to step up to sql server - I have a number of apps holding that number of records and nowhere near approaching the 2GB limit. Processing speed on SQL server is not necessarily any better because of the other demands on its resources (and express only uses one core, same as access) and it may be the network connections are the real problem.

    Around 250,000 records are being updated - two fields, both of which are indexed. Would it help much to remove those indexes?
    depends if the indexes are being used to identify which records are to be updated - if they are then leave them in, if not, take them out and reinstate after the update. But updating indexes adds to the time it takes.

    Linked tables are generally slower than non linked tables, but not that noticeably except when the back end is on another server.

    It may be your update query has not been written efficiently - use of domain functions, nested iifs, indexes applied inappropriately (i.e. indexing fields that are primarily null or have a limited range of values e.g. yes/no fields), inappropriate subqueries, referencing the whole table rather than limiting to those identified as required to change

    A few years ago I tuned an update query which was taking around 12-15 hours to run. By setting it up properly I got it down to around 20 minutes.... so it can be done

    Would reading/updating the records in VBA make it any faster?
    No - as a rule slower, although you can use vba to run a sequence of queries. e.g. make temp table>>>update temp table>>> delete records identified as in temp table from main table>>>insert tables from temp table>>>delete temp table.

    It is in version 2003 (mdb) while I am using 2010. In one attempt I converted it to 2010 to see if that would help but converting it back took an entire day (in fact I think I gave up on it)
    Always work off a copy - there would be no need to change back. ACE (2007+engine) is not significantly quicker than JET but has additional functionality.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Only the data is being updated - 2 fields, both of which are indexed, are having their values changed (e.g. from "106" to "708" and from "123456789" to "987654321").

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Thanks for your great answers. This is my query:

    Code:
    UPDATE tblCOSSImportHistory INNER JOIN tmpCC_ConvHist ON tblCOSSImportHistory.ID = tmpCC_ConvHist.ID SET tblCOSSImportHistory.[Loan Number] = [tmpCC_ConvHist].[NewLoanNumber], tblCOSSImportHistory.[MSP Site Code] = [tmpCC_ConvHist].[NewClient];

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by aytee111 View Post
    Not sure what you mean by "using a query object" as opposed to running a query from the db window...
    Double clicking a query from the Navigation Pane is using a Query Object that is defined and part of the QueryDefs collection. These perform faster than using a string defined in VBA and applying that string to a CurrentDB.Execute method.

    So, if you need to manage dynamic query statements AND manage performance issues, apply the SQL Statement that is defined by your VBA string to the SQL property of a Query Object. You can manage Query Objects via DAO and the QueryDefs collection.

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh yeah, that makes sense.

    Temporarily removing the index and then putting it back later is not something I would choose to do, in my experience Access is not very efficient at that task. I was hoping it would make things better but in all probability takes the same time - if not more - overall.

    "Stay away from VBA' - nothing that complex required here, but good to know.

    Is "DoCmd.RunSQL" the same as "CurrentDB.Execute" as far as performance goes?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    UPDATE tblCOSSImportHistory INNER JOIN tmpCC_ConvHist
    ON tblCOSSImportHistory.ID = tmpCC_ConvHist.ID
    SET tblCOSSImportHistory.[Loan Number] = [tmpCC_ConvHist].[NewLoanNumber],
    tblCOSSImportHistory.[MSP Site Code] = [tmpCC_ConvHist].[NewClient];
    With this query, the fields that need to be indexed are the two ID fields.

    I presume tmpCC_ConvHist is a table, not a query. If a table, have you indexed the ID field?

    If Loan Number and MSP Site Code are indexed recommend temporarily remove the indexes, otherwise ensure that

    NewLoanNumber and NewClient are both indexed and include a criteria

    WHERE tblCOSSImportHistory.[Loan Number] <> [tmpCC_ConvHist].[NewLoanNumber] OR tblCOSSImportHistory.[MSP Site Code] <> [tmpCC_ConvHist].[NewClient]

    Temporarily removing the index and then putting it back later is not something I would choose to do, in my experience Access is not very efficient at that task
    I've never had a problem. With 6m records, index is removed in no time at all and takes less than a minute to recreate. Are you saying it doesn't create an accurate index?

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I presume tmpCC_ConvHist is a table, not a query. If a table, have you indexed the ID field?
    Yes, that is table and no, the ID field on the tmp table isn't indexed (it is on the other side). The ID is an autonumber so I don't need the criteria.

    Yes, both the fields being updated are indexed. What I said about removing the index - the other day I changed the size of the two fields in question in the hope that my query joins would be more efficient. Again, took forever and sat with the message "Creating and changing indexes" for a couple of hours. That has made me hesitant to touch anything.

    Are you saying it doesn't create an accurate index?
    No, I have absolute faith in Microsoft!!

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    huge difference between changing an index v deleting it - change the field size - save the table - then add the index back in. Take a copy and try it - any field will do that has a good variety of values.

    The ID is an autonumber so I don't need the criteria.
    criteria? autonumber does not mean it is indexed. Add the index in and see how it goes. no wonder your query is slow. With 6m records, reckon that is probably 90% of the reason.

    And whist doing all this, do work off a copy so you don't have to undo anything until you understand how it works

  14. #14
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Like I said, experimenting is no fun at all, one thing every 24 hours! This is in pre-production, in development I had no need for these shenanigans, much to do with network speed as you stated. I will add the index to the temp table and see how it goes.

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't suppose you work for chase bank do you? some of this nomenclature seems familiar. I'll be interested in the outcome of this thread so please post your results if you're successful.

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

Similar Threads

  1. Replies: 16
    Last Post: 01-18-2016, 03:58 AM
  2. Speeding up record search
    By Paul H in forum Forms
    Replies: 22
    Last Post: 11-03-2015, 06:44 PM
  3. speeding up queries
    By frustratedwithaccess in forum Access
    Replies: 13
    Last Post: 10-10-2014, 12:08 PM
  4. Speeding up Table Linking Times
    By cbh35711 in forum Access
    Replies: 2
    Last Post: 03-27-2012, 03:54 PM
  5. Speeding up Macros
    By salisbut in forum Programming
    Replies: 3
    Last Post: 07-19-2010, 04:02 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