Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Efficiency when using Union All

    I am trying to perform a query in SQL that will merge the contents of various table, using search criteria to narrow down the results. All of the tables have the exact same fields, and the search criteria is the same for all of them. What I am looking for is the most efficient way to perform this. I am using SQL stored procedures, and passing the results into my code. Note that in my real-world scenario there are more than three tables. Also, don’t worry about format correctness here.

    1) Merge all the tables into one and then perform the search criteria:
    SELECT a, b, c FROM
    (SELECT a, b, c FROM Table1
    UNION ALL
    SELECT a, b, c FROM Table2
    UNION ALL
    SELECT a, b, c FROM Table3) AS MY_MERGE
    WHERE {SEARCH CRITERIA}

    2) Perform the search criteria on each table and merge the results
    SELECT a, b, c FROM
    (SELECT a, b, c FROM Table1 WHERE {SEARCH CRITERIA}
    UNION ALL
    SELECT a, b, c FROM Table2 WHERE {SEARCH CRITERIA}
    UNION ALL
    SELECT a, b, c FROM Table3 AS MY_MERGE WHERE {SEARCH CRITERIA})

    Glenn

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are the search criteria SARG-able? If yes, then I would go with option 2. Even then, the optimizer may reduce the plan to effectively option 2, anyway. Look over the query plans for each type of query, and see what differences there are.

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    The tables themselves are identical, in terms of their construction. The data is in separate tables for different access situations. My query needs all of the data merged together.

    And excuse my ignorance, but what do you mean by SARG-able? Do you mean can there be any search criteria added to any of the tables? If so, then no; there are no parameters to reduce the search set.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SARG is short for Search ARGument.

Tags for this Thread

Posting Permissions

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