none
Query optimization with multiple query join

    Question

  • I have 4 queries each with an inner join.

    I have a WHERE clause on the 1st query only for a column which is in each query and that is the joined column.

    Will I receive faster retrieval if I have the WHERE clause in all queries or is the ACE engine smart enough to detect this and optimize as if there was a where clause in there for each of them anyway ?

    Wednesday, December 18, 2013 7:15 PM

Answers

  • >>Essentially I broke up a single query so that maintenance would be easier.<<

    That means your "final" Query uses the other Queries as its DataSources which was not mentioned in your first post and therefore, it sounded like 4 independent Queries.

    In general, Access will combine the SQL Strings into one and execute the combined SQL String and not one Query after another.  Thus, there should not be any difference whether you include the criterion in the "final" Query or all the component Queries.

    OTOH, it always best to test the 2 set-ups (1 huge SQL/Query vs a set of 4 Queries) with a reasonably-sized test dataset to check it out for yourself.

     


    Van Dinh

    Thursday, December 19, 2013 5:12 PM

All replies

  • Reading your post, the obvious question is what the 4 independent Queries got to do with each other???

    It sounds to me that the 4 Queries are combined in some way but you did not described how you used them together???

    Perhaps, you need to describe the relevant Tables used in the 4 Queries and the name and the SQL String of each of the 4 Queries and how you use this set of 4 Queries.

     


    Van Dinh

    Wednesday, December 18, 2013 10:20 PM
  • I have 4 queries each with an inner join.

    I have a WHERE clause on the 1st query only for a column which is in each query and that is the joined column.

    Will I receive faster retrieval if I have the WHERE clause in all queries or is the ACE engine smart enough to detect this and optimize as if there was a where clause in there for each of them anyway ?

    Hi Syswizard,

    When I have questions about efficiency, I normally do a test where I compare the time used for the first alternative to the time used for the second alternative.
    I am not afraid to make loops, so that I can make the comparison between 1000 time the first alternatives and 1000 times the second alternative. Or 10,000 times, or 100,000 times, or 1,000,000 times ...

    In general, these tests are very instructive.

    Imb.

    Wednesday, December 18, 2013 11:02 PM
  • Essentially I broke up a single query so that maintenance would be easier.

    The total of all 4 queries is 400 lines.

    I think the actual SQL source is irrelevant here...I'm interested in the query engine's logic.
    If it's "smart", it should treat the inner join as an implicit where clause on the joined columns.

    Thursday, December 19, 2013 2:28 PM
  • >>Essentially I broke up a single query so that maintenance would be easier.<<

    That means your "final" Query uses the other Queries as its DataSources which was not mentioned in your first post and therefore, it sounded like 4 independent Queries.

    In general, Access will combine the SQL Strings into one and execute the combined SQL String and not one Query after another.  Thus, there should not be any difference whether you include the criterion in the "final" Query or all the component Queries.

    OTOH, it always best to test the 2 set-ups (1 huge SQL/Query vs a set of 4 Queries) with a reasonably-sized test dataset to check it out for yourself.

     


    Van Dinh

    Thursday, December 19, 2013 5:12 PM
  • That means your "final" Query uses the other Queries as its DataSources which was not mentioned in your first post and therefore, it sounded like 4 independent Queries.


    Van Dinh

    Yes, my bad....sorry about that. Sleep depravation does that to oneself.
    Thursday, December 19, 2013 6:47 PM
  • Only a 5% improvement in speed if there are where clauses in each of the independent queries.

    Next up: single query (I need to get the syntax right on 30 joins !!!!)

    Thursday, December 19, 2013 9:11 PM
  • 30 joins???   I'm having a difficult time trying to imagine what all those tables represent.

    What kind of table structure do you have where joining thirty tables is useful?

    Thursday, December 19, 2013 11:41 PM
  • Marshall....it was somewhat of a design issue......

    Instead of a separate table, a decision was made to to hold 3 instances of an entity....in denormalized form.
    Joining on just 5 columns of those instances consumes 15 joins alone.

    Friday, December 20, 2013 2:16 PM
  • My mind is thoroughly boggled!
    Friday, December 20, 2013 11:19 PM