Results 1 to 15 of 15
  1. #1
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12

    Need suggestions on intensive Access processing problem

    Does anyone has any suggestions on how to improve performance on a processing intensive Access application. There is a small frontend that is about 9 MB. There are 2 backend databases. One with static data that is almost 1 GB. The main table is 3 million rows of stock option quotes. The other backend database is 31 MB and contains tables that grow by a couple thousand records during the run. The main processing is doing queries against the 3 million row table. The run currently takes about 5 hours. All the tables have primary keys and indexes matching the where conditions in queries. Any thoughts or suggestions are appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What happens during this "run"? Is querying the data taking a long time, or looping it or something? In general, executing SQL will be more efficient than looping a table with VBA.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hmm... that is slow

    I have a similar situation with a UK postcodes app - Access FE of around 10MB with two Access BE files - one with static postcodes data of approx 1.6GB mainly comprising 1 table of 2.6 million records and 44 fields.
    The other is only a few MB but also growing

    The reason why I've given the same amount of detail is that searches take no more than a couple of seconds & often less than a second

    You've already done some of the obvious things - but consider the following as well:
    Index all fields used in searching.
    If possible break down searches using cascading combo boxes to limit the search 'pool' each time
    If using SQL, use DB.Execute rather than DoCmd.RunSQL
    Use the JET ShowPlan to optimise your queries
    Consider moving the large BE to SQL Server so you can just filter the data you need at the server rather than grabbing it all then filtering in Access
    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

  4. #4
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    "Index all fields used in searching."
    I have built indexes matching the fields in the where clause. You think its better to just index the individual fields and let Jet use multiple indexes?

    "If possible break down searches using cascading combo boxes to limit the search 'pool' each time"
    This is an automated process. There is no user involved.

    "If using SQL, use DB.Execute rather than DoCmd.RunSQL"
    Yes, I use Execute for the action queries.

    "Use the JET ShowPlan to optimise your queries"
    Didn't know about that, thanks.

    "Consider moving the large BE to SQL Server so you can just filter the data you need at the server rather than grabbing it all then filtering in Access"
    I don't a separate server to put SQL Server on. I could run it on the same PC/processor as Access but am not sure that would help.

    Thanks for the post.

  5. #5
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    What's its doing is complicated and hard to explain. Basically, it's processing 400 trades for a trading system with associated rules. It is doing some looping of recordsets and also a lot of queries against the 3 million row table. There is a lot of logic coded in VBA. It is too involved to do without a lot of VBA.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Responses in BLUE
    Quote Originally Posted by BrooksR View Post
    "Index all fields used in searching."
    I have built indexes matching the fields in the where clause. You think its better to just index the individual fields and let Jet use multiple indexes?
    I can't tell - you certainly could try that

    "If possible break down searches using cascading combo boxes to limit the search 'pool' each time"
    This is an automated process. There is no user involved.
    OK but some method of breaking down the search to reduce the number of records being searched

    "If using SQL, use DB.Execute rather than DoCmd.RunSQL"
    Yes, I use Execute for the action queries.

    "Use the JET ShowPlan to optimise your queries"
    Didn't know about that, thanks.
    See this link http://www.mendipdatasystems.co.uk/show-plan-go-faster/4594460516 and also read the linked article by Susan Haskins

    "Consider moving the large BE to SQL Server so you can just filter the data you need at the server rather than grabbing it all then filtering in Access"
    I don't a separate server to put SQL Server on. I could run it on the same PC/processor as Access but am not sure that would help.
    I meant use a SQL Server BE database - you can host that on the same PC oor on a network server
    it won't immediately transform the time needed but it is designed for large scale data & has many features that will allow more efficient retrieval of required data


    Thanks for the post.
    It is doing some looping of recordsets and also a lot of queries against the 3 million row table.
    Looping through large recordsets is very slow as the changes are done row by agonising row
    I realise its not always possible but if any/most of those can be done using queries or SQL equivalents it will MASSIVELY reduce the time needed

    The best speed reduction I ever achieved was a procedure that originally took 35 minutes. It now takes about 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

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    other things to consider. In addition to indexing criteria, index fields you join or sort on. Don't use domain functions or an initial * in search criteria. Are your queries really optimised - using sub queries can be slow, look to design another way. Sometimes necessary to stage processed data in a temporary table.

    looping of recordsets will be slow compared with doing it in sql. Sometimes using a UDF will be faster than sql, but not always. In my experience there is very little you can do in VBA that can't be done in (access) SQL - recursive actions for example has to be done in VBA.

    Regards separate server, consider using sql server express which does not need to go onto a separate server. Nothing like your scale but I have one report that takes about 10 seconds when run in Access, but less than 2 seconds when run in SSE due to the fact I can use some of the special transact SQL options.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    let Jet use multiple indexes?
    Jet? you mean ACE? Multiple Indexes? do you mean multi field indexes?

  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
    Also have a look at the last part of this link http://www.mendipdatasystems.co.uk/s...s-6/4594478795 comparing the speed to run a procedure using linked SQL tables with the same procedure using linked Access tables. The latter was 20% slower despite the fact that I had done nothing in SQL Server to optimise the process. If i had done so the difference would have been greater still

    That page is one of a number of speed comparison tests on my website. Might be worth having a quick read of each
    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
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    Quote Originally Posted by ridders52 View Post
    Also have a look at the last part of this link http://www.mendipdatasystems.co.uk/s...s-6/4594478795 comparing the speed to run a procedure using linked SQL tables with the same procedure using linked Access tables. The latter was 20% slower despite the fact that I had done nothing in SQL Server to optimise the process. If i had done so the difference would have been greater still

    That page is one of a number of speed comparison tests on my website. Might be worth having a quick read of each
    Nice website and comparison testing work, Colin. It does surprise me that there is not a greater difference in the methods.

    My situation is different as I'm doings thousands of select queries against a 3 million row table. Unfortunately, it takes 4 fields to make up the primary key (quote date, expiration date, call/put, strike price).

    Thanks to all who posted. I'm going to try out the various ideas and will post back.

    Brooks

  11. #11
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    Quote Originally Posted by Ajax View Post
    Jet? you mean ACE? Multiple Indexes? do you mean multi field indexes?
    Yeah, I guess technically from 2007 onward Jet is now ACE. I've been using it since 1.0, so old habits are hard to break.

    Currently, if I have 3 fields in the where clause of the SQL select statement, I will create an alternate/secondary index with those fields. Novice suggested "index all fields used in searching", so I wondered whether he/she thought it would be better to have 3 indexes of 1 field each vs. 1 index of 3 fields.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    better to have one index per field - multi field indexes are usually only to maintain rules - no duplicates for example.

  13. #13
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    RESOLVED

    Changes made to speed up Access query processing:

    1. Replaced DLookup with Allen Browne’s ELookup. http://allenbrowne.com/ser-42.html
    2. Replaced indexes containing multiple fields with indexes on individual fields. Even when these seemed equivalent, Access seemed to run faster with the individual field indexes.
    3. Replaced multiple DLookups against the same table with a DAO recordset containing all the needed fields. This is obvious when coding from scratch but the DLookups had been added over time and the cumulative effect of their slowdown had not been realized.


    Thanks again to everyone who posted ideas.
    Brooks

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please let us know what the new "run time" is compared to the previous "slow" performance.

  15. #15
    BrooksR is offline Novice
    Windows 10 Access 2007
    Join Date
    Nov 2018
    Posts
    12
    Quote Originally Posted by orange View Post
    Please let us know what the new "run time" is compared to the previous "slow" performance.
    Some of the run times were reduced by 75%.

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

Similar Threads

  1. processing e-mails from outlook into access
    By Mlki in forum Programming
    Replies: 1
    Last Post: 07-11-2016, 09:40 AM
  2. Replies: 6
    Last Post: 04-03-2015, 08:36 AM
  3. Replies: 3
    Last Post: 06-24-2010, 07:18 PM
  4. Strange issue with Access 2002 - Any suggestions?
    By mlopezkimco in forum Access
    Replies: 0
    Last Post: 07-30-2008, 09:25 AM
  5. I have Problem in processing Dlookup Function
    By Katada in forum Programming
    Replies: 2
    Last Post: 04-23-2006, 12:07 AM

Tags for this Thread

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