Results 1 to 7 of 7
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    What's best for performance? Query with 18 fields with each 6 fields having 6 rows of criteria.

    Better description: I have a query that takes about 5 - 7 minutes to run. The query has 18 fields. 6 of those fields have 6 rows of criteria. Some fields have expressions as well. What are some suggestions on speeding this query up? I know it'd be a good idea to avoid a lot of expressions and a numerous amount of fields along with multiple criteria, but I cannot seem to find a better way around this. I don't want have to run multiple queries to get the same results of this single query, but all options are on the table at this point. The reason I ask this is because I am going back through all my queries and seeing what improvements I can make for better performance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could post the SQL you are describing.

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I apologize, but it's always tough posting SQL because I cannot post any of the SQL due to names in the DB being proprietary with our software providers for the DB.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    do your tables have indexing for the fields you are joining on and applying criteria?

    apologize, but it's always tough posting SQL because I cannot post any of the SQL due to names in the DB being proprietary with our software providers for the DB.
    No problem, copy your sql to word or notepad, then use find and replace to change the field names to something still meaningful but not the same as your software supplier.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You mean 6 criteria rows in the query design grid? Are they dynamic (input popup or referencing form controls)?

    I never use dynamic parameterized queries. I prefer VBA to build filter criteria string for OpenForm/OpenReport method. Review http://allenbrowne.com/ser-62.html

    For further help, you are going to have to provide a framework we can work with - example data and attempted SQL.
    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.

  6. #6
    Tom Enders is offline Novice
    Windows XP Access 2016
    Join Date
    Mar 2018
    Posts
    3
    If you use a field in Order By, Where, Group By, Sorting, or apply any expression to it in any query you should have an index on it. This will speed of the query if you're already doing this then I'd look at simplifying the criteria in some way. With a query this complex you might be best off to use multiple queries (I know you said you don't want to) but often you'll find that some of the simplifies queries are already written anyway to perform other functions in the database.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without know how well your query has been constructed we are just throwing ideas around. There are lots of things you can do in the sql window you cannot do in the query builder - in particular changing joins from = to >=, etc or using a calculated value on one or both sides

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

Similar Threads

  1. Replies: 3
    Last Post: 02-20-2017, 08:27 AM
  2. Replies: 5
    Last Post: 02-13-2016, 10:12 AM
  3. Sum up fields in different rows to a new field in a query
    By Jan e Porsböll in forum Queries
    Replies: 1
    Last Post: 08-15-2014, 07:05 AM
  4. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  5. Replies: 3
    Last Post: 03-26-2012, 01:29 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