Multiple UNION in SQL 2016

  • datsun

    SSCrazy

    Points: 2289

    Hello,

    We have SQL 2016 SP2.

    Is there a problem of running a View or Query with multiple UNION (we have 5 in one view), in terms of performance and execution plan?

    It takes around 40 min. to run the query on SQL 2016 SP2 but takes seconds on the same dataset on SQL 2008 R2.

    When I run individual queries before and after the UNION they are fine on SQL 2016, but as a group takes awful time.

    thanks,

    Vinay

  • pietlinden

    SSC Guru

    Points: 62456

    Multiple UNIONs? That's a recipe for a long long wait. Can you post the query?

    You can try using UNION ALL between the individual queries instead of UNION. UNION will remove duplicates, and if you don't need that, don't do it. But you may need to rewrite the query to avoid them.

    If you're querying the same table in each of the UNIONed sets, then use OR to combine the filters.

    • This reply was modified 3 months, 2 weeks ago by  pietlinden. Reason: forgot about the OR
  • datsun

    SSCrazy

    Points: 2289

    Thank you. Personally I don't like UNIONs too. But these are user queries/views I have to test on SQL 2016 as we are in process of migration from SQL 2008 R2.

    Why would it happily work on the same dataset in SQL 2008 but not in SQL 2016?

    I can't go and edit all the queries to change Union to alternatives, unless there is a valid reason in SQL 2016 for its poor performance.

    In some query outputs Union ALL can cause problem as removing duplicates at an early stage can stop problems elsewhere.

    thanks,

    Vinay

  • nova

    Valued Member

    Points: 70

    Vinay,

    Have you compared the execution plans in SQL Server 2008 and 2016? Is the plan the same?

  • pietlinden

    SSC Guru

    Points: 62456

    There's a new cardinality estimator in SQL 2016.  Force your query to use the old one. See: https://stackoverflow.com/questions/47215135/facing-performance-issues-in-sql-server-2016-after-migration

  • datsun

    SSCrazy

    Points: 2289

    Nova,

     

    The execution Plans do look identical. It's quite a big one, so hard to compare every node. It starts off with Hash match (Union) cost 0% 7 of them in both servers but a node of "parallelism" is added before the final SELECT in SQL2016.

    The Estimated Subtree cost is higher in "2008" than in "2016", but it's the SQL 2016 which is slower in execution.

    Not sure what to look for when comparing two plans.

    Thanks,

    Vinay

     

  • Andrey

    SSChasing Mays

    Points: 645

    datsun wrote:

    Not sure what to look for when comparing two plans. 

    let SSMS do it for you

    https://blogs.msdn.microsoft.com/sql_server_team/comparison-tool-released-with-latest-ssms/

     

  • datsun

    SSCrazy

    Points: 2289

    There's a new cardinality estimator in SQL 2016.  Force your query to use the old one. See: https://stackoverflow.com/questions/47215135/facing-performance-issues-in-sql-server-2016-after-migration%5B/quote%5D

    That worked perfectly fine using the Option Hint directly in the final Select query from the View. Thank you.

    "CARDINALITY_ESTIMATION" is the answer.

    many thanks,

    Vinay

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply