Multiple UNION in SQL 2016

Question

Multiple UNION in SQL 2016

datsun

SSCrazy

Points: 2397
More actions
July 3, 2019 at 1:58 pm

#3659762

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

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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 5 years, 4 months ago by pietlinden. Reason: forgot about the OR

datsun SSCrazy Points: 2397 More actions · Answer 2

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 SSC Journeyman Points: 80 More actions · Answer 3

Vinay,

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

pietlinden SSC Guru Points: 63503 More actions · Answer 4

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: 2397 More actions · Answer 5

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 SSC Eights! Points: 840 More actions · Answer 6

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: 2397 More actions · Answer 7

pietlinden wrote:

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