Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Location
    London
    Posts
    87

    Unanswered: Optimising Union Queries

    Is there a performance benefit in Including all the SQL of the sub-queries in a union query rather that just joining the sub-query results

    eg using a single query

    SELECT Sum(tblOne.FieldA), Max(tblTwo.FieldB).......................
    FROM tblOne INNER JOIN .............
    WHERE ........
    GROUP BY ......
    HAVING .......
    UNION ALL
    SELECT ............etc

    rather than a union and subqueries

    SELECT sqryOne.Field1, sqryOne.Field2...........
    FROM sqryOne
    UNION ALL
    SELECT sqryTwo.Field1, sqryTwo.Field2.............
    FROM sqryTwo;

    I'm assuming the answer is yes, unless JET aggregates all this SQL and optimises it to the same effect, in which case one should use the latter approach for clarity.


    Thanks in advance, Risky.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Query optimizers can be inscrutable. Sometimes you just have to write it both ways and see which is faster. Make sure you test with a representative number of records, as the optimizer may devise different plans for large tables than for small tables.

    All other things being even, clarity is important!

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •