none
Optimization of compiled queries RRS feed

  • Question

  • Generating a SQL string at run-time, one loses the performance advantage of compiled queries and the ability to quickly and easily edit using the query designer (due to Access' insistence on spraying parentheses everywhere in its SQL I find this a necessary tool).

    ...

    -Bruce

    I can imagine that in compiled queries there can be a re-arrangement of the different joins, making use of the defined keys, and then by adding the parentheses, give an optimized sql-string.

    Are there any further ("deeper") optimizations done in the compiling process?

    Imb.

    Friday, October 28, 2016 8:53 AM

Answers

  • Yes, there are many more choices for the query plan.

    For example, the query plan might choose to do a full table scan EVEN when an index is available (since reading data in table order can occur very fast as opposed to jumping around and pulling records with an index).

    And it gets even more complex.

    If you build a SQL string in code or at runtime, then often the results are better and faster!

    That SQL string at runtime MUST be compiled and processed into that complex query plan.

    The fact that you use a saved query (that is pre-compiled and “already” has a query plan built) or force the query plan each time the SQL is run? Not an issue!

    Well, given that the compile time takes about 0.01 seconds with today’s “modern” desktops that have more mojo then a huge mainframe of just 15 years ago (when that advice to use compiled quires was given to you!), then it really is a moot point. Remember, even in just say 15 years, we 10x or more times faster in terms of CPU.

    Let’s do the math:

    Query compile time 0.01

    Time to run query: 10 seconds

    So you now go off and read some cool paper about how you can eliminate the compile time of the query. You spend all that human time pulling out some SQL in code, and moving it to a saved query. This effort can take considerable HUMAN time, and you only going to save that 0.01 seconds in the process!

    You will never reclaim that human time in your life (and you have a limited lifespan – so live life to the fullest!).

    So using a saved query may result in the query plan NOT having to be generated – and that will run a 1000 times faster. The problem is the WHOLE time is only so tiny! I mean having something run 1000 times faster for a task that only takes 0.01 seconds NEVER really going to make a difference, is it?

    However, it turns out for JET (now ACE), you are OFTEN better to compile on the fly and without parameters!

    Why? Well, because then EACH time you run the query you will have a fresh brand new query plan and optimizing occur!!

    In fact, I seen this, and you can gain 30% or even 40% improvement in performance due to ALWAYS having a fresh query plan in place of using a saved query that does NOT re-compile each time. More specific, the performance increase I speak of has to do with parameters in a query.

    I shall quote from the JET white paper on performance:
    To quote from the JET white paper page 18, we get:

    <quote>
    Check Parameterized Queries for Optimal Performance
    Parameterized queries can only be implemented by using a stored query. Since stored queries have a precompiled query plan, parameterized queries that contain parameters on indexed columns may not execute efficiently. Since the query engine does not know the values to be passed in a parameter in advance, it can only guess as to the most efficient query plan. Based on customer performance scenarios that we have examined, we have discovered that in some instances substantial performance gains can be achieved by replacing a stored parameterized query with a temporary query. This means creating the SQL string in code and passing it to the DAO OpenRecordset or Execute methods of the Database object.
    </quote>

    Wow!!!! Just wow!!!

    To translate the above in plain English: What this means I that jet with a given query plan has to make guesses when you use parameters. If you use VBA code and built the whole sql string WITHOUT parameters then then the jet database engine will not make those bad guesses and create a BAD query plan anymore!

    In other words, by using in-line SQL in code, and not having parameters, you force jet to re-build the query plan and AVOID a pre-compiled query that makes bad guesses.

    Since the vast amount of time is PULLING the actual data, then today with MASSIVE amounts of processing, then the query time is moot and can be ignored. I mean, is starting up your car the huge issue in a trip, or the driving of the trip?

    The defining choice here is what costs LESS human and developer time – people are expensive!

    You never re-gain that life time for the efforts and time it saves. As noted, the query compile time is small likely about 0.01 of a second.

    So for a given query, pre-compiled or not, you never notice the query compile time. But as the white paper on performance shows above, there are SUBSTANTIALLY gains to be made by advoiding saved pre-compiled queries with parameters.

    I can confirm the above performance advantages in the limited context of that white paper.

    Bottom line:

    The query compile and building of the query plan and the database engine parsing the SQL syntax, and then building a query plan to figure out if full table scans are better vs using indexing etc. (and many more pages of how such query plans are created and optimized), occurs so fast that you really don’t have to care.

    The overall choice here is to simply IGNORE the time to build such a query plan – it is tiny compared to the overall executing time to pull data.

    So do what is most convenient for you the human – your time is far most costly and any worry about the time to create those complex query plans can be ignored in 99% of cases.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen LiModerator Tuesday, November 1, 2016 7:39 AM
    • Marked as answer by Imb-hb Wednesday, November 2, 2016 10:15 AM
    Friday, October 28, 2016 3:46 PM

All replies

  • It determines is execution plan.  

    http://www.devhut.net/2011/04/30/ms-access-jet-showplan/

    On today's computers, this is less of a concern, but always a good thing to optimize as best you can.  If you have no need to dynamically create queries, recordsources, ... then don't, otherwise i wouldn't worry about it.


    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, October 28, 2016 11:57 AM
  • Yes, there are many more choices for the query plan.

    For example, the query plan might choose to do a full table scan EVEN when an index is available (since reading data in table order can occur very fast as opposed to jumping around and pulling records with an index).

    And it gets even more complex.

    If you build a SQL string in code or at runtime, then often the results are better and faster!

    That SQL string at runtime MUST be compiled and processed into that complex query plan.

    The fact that you use a saved query (that is pre-compiled and “already” has a query plan built) or force the query plan each time the SQL is run? Not an issue!

    Well, given that the compile time takes about 0.01 seconds with today’s “modern” desktops that have more mojo then a huge mainframe of just 15 years ago (when that advice to use compiled quires was given to you!), then it really is a moot point. Remember, even in just say 15 years, we 10x or more times faster in terms of CPU.

    Let’s do the math:

    Query compile time 0.01

    Time to run query: 10 seconds

    So you now go off and read some cool paper about how you can eliminate the compile time of the query. You spend all that human time pulling out some SQL in code, and moving it to a saved query. This effort can take considerable HUMAN time, and you only going to save that 0.01 seconds in the process!

    You will never reclaim that human time in your life (and you have a limited lifespan – so live life to the fullest!).

    So using a saved query may result in the query plan NOT having to be generated – and that will run a 1000 times faster. The problem is the WHOLE time is only so tiny! I mean having something run 1000 times faster for a task that only takes 0.01 seconds NEVER really going to make a difference, is it?

    However, it turns out for JET (now ACE), you are OFTEN better to compile on the fly and without parameters!

    Why? Well, because then EACH time you run the query you will have a fresh brand new query plan and optimizing occur!!

    In fact, I seen this, and you can gain 30% or even 40% improvement in performance due to ALWAYS having a fresh query plan in place of using a saved query that does NOT re-compile each time. More specific, the performance increase I speak of has to do with parameters in a query.

    I shall quote from the JET white paper on performance:
    To quote from the JET white paper page 18, we get:

    <quote>
    Check Parameterized Queries for Optimal Performance
    Parameterized queries can only be implemented by using a stored query. Since stored queries have a precompiled query plan, parameterized queries that contain parameters on indexed columns may not execute efficiently. Since the query engine does not know the values to be passed in a parameter in advance, it can only guess as to the most efficient query plan. Based on customer performance scenarios that we have examined, we have discovered that in some instances substantial performance gains can be achieved by replacing a stored parameterized query with a temporary query. This means creating the SQL string in code and passing it to the DAO OpenRecordset or Execute methods of the Database object.
    </quote>

    Wow!!!! Just wow!!!

    To translate the above in plain English: What this means I that jet with a given query plan has to make guesses when you use parameters. If you use VBA code and built the whole sql string WITHOUT parameters then then the jet database engine will not make those bad guesses and create a BAD query plan anymore!

    In other words, by using in-line SQL in code, and not having parameters, you force jet to re-build the query plan and AVOID a pre-compiled query that makes bad guesses.

    Since the vast amount of time is PULLING the actual data, then today with MASSIVE amounts of processing, then the query time is moot and can be ignored. I mean, is starting up your car the huge issue in a trip, or the driving of the trip?

    The defining choice here is what costs LESS human and developer time – people are expensive!

    You never re-gain that life time for the efforts and time it saves. As noted, the query compile time is small likely about 0.01 of a second.

    So for a given query, pre-compiled or not, you never notice the query compile time. But as the white paper on performance shows above, there are SUBSTANTIALLY gains to be made by advoiding saved pre-compiled queries with parameters.

    I can confirm the above performance advantages in the limited context of that white paper.

    Bottom line:

    The query compile and building of the query plan and the database engine parsing the SQL syntax, and then building a query plan to figure out if full table scans are better vs using indexing etc. (and many more pages of how such query plans are created and optimized), occurs so fast that you really don’t have to care.

    The overall choice here is to simply IGNORE the time to build such a query plan – it is tiny compared to the overall executing time to pull data.

    So do what is most convenient for you the human – your time is far most costly and any worry about the time to create those complex query plans can be ignored in 99% of cases.


    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Chenchen LiModerator Tuesday, November 1, 2016 7:39 AM
    • Marked as answer by Imb-hb Wednesday, November 2, 2016 10:15 AM
    Friday, October 28, 2016 3:46 PM
  • Albert,

    I always love it when you give detailed answers with such explanation and even translate a white paper's content into actual pain English!

    Thank you for sharing.


    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, October 28, 2016 4:24 PM
  • You will never reclaim that human time in your life (and you have a limited lifespan – so live life to the fullest!).

    So using a saved query may result in the query plan NOT having to be generated – and that will run a 1000 times faster. The problem is the WHOLE time is only so tiny! I mean having something run 1000 times faster for a task that only takes 0.01 seconds NEVER really going to make a difference, is it?

    Hi Albert,

    Thank you for your answer with the technical ànd philosophical observations. I am still digesting all the information, and trying to understand how all this works.

    I wil mark your reply as an answer in a couple of days. In the meantime I will wait for some other comments.

    Imb.

    Friday, October 28, 2016 8:58 PM
  • On today's computers, this is less of a concern, but always a good thing to optimize as best you can.  If you have no need to dynamically create queries, recordsources, ... then don't, otherwise i wouldn't worry about it.

    Hi Daniel,

    Thank you. Not worrying, just trying to understand ...

    Imb.

    Friday, October 28, 2016 8:59 PM
  • I wil mark your reply as an answer in a couple of days. In the meantime I will wait for some other comments.

    Hi Albert,

    Done. Thank you.

    Imb.

    Wednesday, November 2, 2016 10:18 AM