none
Efficiency of basing a query on another saved query RRS feed

  • Question

  • If I save 2 queries (call them query1 and query2) which include a SUM field called "totals".

    Then I create another query based off them, with a where clause:

    SELECT query1.totals + query2.totals 
    FROM query1 INNER JOIN query2
    ON query1.pickupDateID = query2.pickupDateID
    WHERE query1.ItemID = 55 AND query2.ItemID = 55 
    Does access calculate query1 and query2 for all entries, or will it apply the WHERE clause to only calculate them for ItemID 55?

     

    Monday, October 31, 2016 5:04 PM

Answers

  • If I save 2 queries (call them query1 and query2) which include a SUM field called "totals".

    Then I create another query based off them, with a where clause:

    SELECT query1.totals + query2.totals 
    FROM query1 INNER JOIN query2
    ON query1.pickupDateID = query2.pickupDateID
    WHERE query1.ItemID = 55 AND query2.ItemID = 55 
    Does access calculate query1 and query2 for all entries, or will it apply the WHERE clause to only calculate them for ItemID 55?

    When you run a query that involves other queries, the query engine examines the whole assembled query, including the other queries, and builds a unified query plan.  That will normally include applying restrictions to the source tables -- as used by query1 and query2 in your example -- as early in the process as the query engine thinks is likely to be most efficient.  In your case, the most likely query plan will be to restrict the rows of both source tables (used by query1 and query2) before applying the join.  So the answer to your question, "will it apply the WHERE clause to only calculate them for ItemID 55", is yes -- usually.

    The reason I use weasel-words like "usually" and "normally" is that the the query engine doesn't always come up with the query plan you'd expect.  Certain query characteristics can affect its choice of execution plan, and even in the absence of those characteristics I've seen it (rarely) come up with an execution plan that is grossly inefficient -- plainly mistaken, compared with the one a human would have suggested.  Sometimes, tinkering with the query in small ways can get it to devise a better execution plan; sometimes not (or at least, on very rare occasions I haven't been able to find a way).

    You can get Access to show you the execution plan for most types of query by setting the JETSHOWPLAN registry key to "ON", creating this key if necessary.  If you want to play around with this, you may want to read Daniel Pineault's article here:  http://www.devhut.net/2011/04/30/ms-access-jet-showplan/ .  However, I'd like to note that, with versions of Access from 2007 forward, the base key is not in \\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines, but in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\<version>\Access Connectivity Engine\Engines\Debug.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jacob CE Tuesday, November 1, 2016 6:21 PM
    Tuesday, November 1, 2016 5:09 PM

All replies

  • Hi,

    Just a guess, but I think query1 and query2 will have to be resolved first before the criteria will be applied. If you want, you can modify query1 and query2 to apply the criteria first.

    Just my 2 cents...

    Monday, October 31, 2016 5:11 PM
  • Thanks.

    I can't, because I'm going to be using query3 in my VBA with different ItemIDs.

    Would it help if I wrote out query1 and query2 fully as subqueries in the select part of query 3? It would be quite messy, but would be worth it.

    Monday, October 31, 2016 5:39 PM
  • Thanks.

    I can't, because I'm going to be using query3 in my VBA with different ItemIDs.

    Would it help if I wrote out query1 and query2 fully as subqueries in the select part of query 3? It would be quite messy, but would be worth it.

    Hi,

    I don't see why you can't add the criteria in the first two queries, but as you said, you can reconstruct the whole query in VBA and not worry about the saved queries.

    Another approach is to use a temporary query, which you can modify via code to only return the needed data before you call/use it in your VBA. For this approach, check out the QueryDef object.

    Hope it helps...

    Monday, October 31, 2016 7:28 PM
  • How about using a parameter for query 3? You can setup a long integer parameter named something like [DesiredOrderId] and apply that parameter in the Where clause. I believe that Access merges the sql in Query 1 and Query 2 is with the sql in query 3 and then executes the resulting single sql statement, rather than independently executing each of the subqueries. I know that's what SQL Server does, but I'm not positive about Access. I don't think you pay a performance penalty for using these subqueries, but if you have a large enough data volume that it might matter (or really, if the execution time is currently a problem), you could test writing it as a single sql statement.

    You might also try moving the query1.ItemID = query2.ItemID condition into the Join clause and just using query1.ItemID = [DesiredOrderId] in the Where clause. If you have a large data volume, make sure the underlying table(s) have indexes on { orderId, pickupDate }.


    Paul

    Tuesday, November 1, 2016 12:31 PM
  • If I save 2 queries (call them query1 and query2) which include a SUM field called "totals".

    Then I create another query based off them, with a where clause:

    SELECT query1.totals + query2.totals 
    FROM query1 INNER JOIN query2
    ON query1.pickupDateID = query2.pickupDateID
    WHERE query1.ItemID = 55 AND query2.ItemID = 55 
    Does access calculate query1 and query2 for all entries, or will it apply the WHERE clause to only calculate them for ItemID 55?

    When you run a query that involves other queries, the query engine examines the whole assembled query, including the other queries, and builds a unified query plan.  That will normally include applying restrictions to the source tables -- as used by query1 and query2 in your example -- as early in the process as the query engine thinks is likely to be most efficient.  In your case, the most likely query plan will be to restrict the rows of both source tables (used by query1 and query2) before applying the join.  So the answer to your question, "will it apply the WHERE clause to only calculate them for ItemID 55", is yes -- usually.

    The reason I use weasel-words like "usually" and "normally" is that the the query engine doesn't always come up with the query plan you'd expect.  Certain query characteristics can affect its choice of execution plan, and even in the absence of those characteristics I've seen it (rarely) come up with an execution plan that is grossly inefficient -- plainly mistaken, compared with the one a human would have suggested.  Sometimes, tinkering with the query in small ways can get it to devise a better execution plan; sometimes not (or at least, on very rare occasions I haven't been able to find a way).

    You can get Access to show you the execution plan for most types of query by setting the JETSHOWPLAN registry key to "ON", creating this key if necessary.  If you want to play around with this, you may want to read Daniel Pineault's article here:  http://www.devhut.net/2011/04/30/ms-access-jet-showplan/ .  However, I'd like to note that, with versions of Access from 2007 forward, the base key is not in \\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines, but in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\<version>\Access Connectivity Engine\Engines\Debug.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jacob CE Tuesday, November 1, 2016 6:21 PM
    Tuesday, November 1, 2016 5:09 PM
  • Thank you so much!

    That was so clear and so helpful!

    Tuesday, November 1, 2016 6:21 PM