Results 1 to 9 of 9
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159

    Parameter Queries run VERY slow...

    Hello, thank you for considering helping me. It means a lot.

    I have a Query with several fields. 2 of the fields are Criteria fields. The Year field and the Month field. For Year I'll put 2021 and Month I'll put 1. Everything works beautifully. The resultset is almost instantly produced, revealing records with dates within January 2021.

    Then I did this...


    To make the reports more versatile:
    - I changed the Year Field Criteria from 2021 to Forms![frmReports]![cboYear].
    - I changed the Month Field Criteria from 1 to Forms![frmReports]![cboMonth].

    The form is fresh, no code, and no other controls other than the 2 comboboxes used in the aforementioned criteria.

    Everything still works... but holy cow is it slow. I have reports taking 20+ seconds to generate on my rocketship-fast computer.

    What in blazes could cause this simple change to bog speeds down this much?

    Thank you for any help,
    Matt

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

    Have you included the form references in the query parameters? to do this, right click in the upper part of the window and select parameters.
    alternatively, if you open the report using say a button on the form, include the parameters in the where parameter of openreport

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you post up the SQL of the query?
    Are your month and year fields indexed.

    And if you can, why not have a combined date field and simply use MyDate Between Forms![frmReports]!StartDate And Forms![frmReports]!EndDate

    With a fixed criteria the database engine will save a query plan - with form based criteria it may not.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIRC the form reference is evaluated for each record - usually not so bad if there are not other issues such as hundreds of thousands of records.
    I like the parameter idea (can't recall if that means the reference is evaluated once or for each record).
    Alternatively, you could also
    - build the sql in code instead and base the report on it (if it's not too long),
    - alter the query def then open the report. Use either a value for the criteria or a variable containing the criteria value.
    - use the report filter property
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159
    Thank you all for your replies Ajax, Minty, and of course Micron. I finally figured this thing out after hours of screwing around.

    There is some kind of error in Access that causes this, and I don't know or care why anymore.

    I knew things were fishy when I took the combobox references in the Criteria fields in the Query, and changed them back to 2021 and 1 respectively (see original post for a refresher). Weirdly, the Query remains slow, even though I clearly reverted the changes back to their original values.

    The short of it is this: I was running a Query with both linked tables and regular local tables. I was joining them. It was absolutely fine. But sometimes I would change the Criteria in various ways and suddenly the Query times when from instant to 10, 20, 30 seconds or even longer. It got to the point where I would have 2 Queries with 100% identical SQL and one would be instant and another would take 30 seconds to run. Was driving me nuts.

    Eliminating the local table from the query instantly fixes it. Nothing else I tried did... and I tried the kitchen sink approach.

    I seriously considered making a few minute video of this problem because it was so amazing and perplexing, but then I remembered you guys have lives.

    Thank you all again and take care,
    Matt

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    mixing tables from different locations in a query will always be slower to some extent. Not something you mentioned before. Also brings in the whole question of how those tables are linked and where to and what the query actually is.

    The other thing to be aware of is that access will display the first screenful of records when they are available and will continue to bring through other records - it won't be finished until the recordcount figure is displayed at the bottom. Reports on the other hand won't display until all records are returned.

  7. #7
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159
    Hello Ajax. You said:
    "mixing tables from different locations in a query will always be slower to some extent. Not something you mentioned before."

    Good thought - but I left it out on purpose because I knew that wasn't the cause in this case, and I didn't want you guys generously volunteering your time unnecessarily looking into what I knew wasn't an issue. Believe me: The queries were running and returning instant results, even with mixed table types. It wasn't until I changed the Criteria section of the Query (WHERE clauses and HAVING clauses) that everything behaved so weird. Importantly, once I undid the changes to the Criteria section to try and make it fast again, it remained slow. Nothing I could do to the SQL would make that query fast again. I could have 2 queries with character-per-character identical SQL and one would be instant and the other would say "running query" in the lower right status bar for 30 seconds of loading.

    At this time, I'm convinced this is an error on Microsoft's part.

    Matt

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds like the original query plan that you would have created left certain elements in place based around the ODBC. If you copy the sql text from the slow query to a new query (which will create a new query plan), how does that run?

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by MatthewGrace View Post
    It wasn't until I changed the Criteria section of the Query (WHERE clauses and HAVING clauses) that everything behaved so weird. Importantly, once I undid the changes to the Criteria section to try and make it fast again, it remained slow.
    Access (and from memory the ODBC driver), is clever enough to alter a Where and a Having to produce the correct results in the most efficient way.
    So if you used a Having clause in a grouped query that you didn't need to use (it's intrinsically slower) then Access will run that query with a Where clause.

    We did run some speed tests over at https://www.access-programmers.co.uk/forums on a variety of queries and the results were very interesting.

    As Ajax stated, I wonder if your change of the criteria wasn't picked up somehow.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 1
    Last Post: 06-17-2016, 08:12 AM
  2. nested queries extraordinary slow
    By fluppe in forum Access
    Replies: 1
    Last Post: 09-26-2014, 07:44 AM
  3. Update queries sometimes very slow
    By GregL65R in forum Programming
    Replies: 3
    Last Post: 12-05-2013, 05:42 PM
  4. split database queries slow fyi
    By survivo01 in forum Access
    Replies: 1
    Last Post: 08-24-2013, 01:08 AM
  5. Replies: 13
    Last Post: 01-10-2012, 09:56 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