CODEWITHSUNDEEP
sql-serverunion
Loïc Bar
Loïc Bar

Reputation: 51

SQL Server: multiple queries or UNION

I've 16 queries that I need to execute. All these queries have the same format : 

SELECT string, number, number

Make sense for me to group all these data together as it's to create a dashboard with all the results.

My question is : do you think a UNION ALL will be faster then executing all the queries one by one?

Upvotes: 4

Views: 12398

Answers (5)

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

UNION ALL should certainly allow you to get the data back faster.

What are you going to do with the data on the client, though? If you have to split it back it into 16 separate sets of data the overall execution time will probably increase.

Whatever you do, you only want one call to the database so if you leave it as 16 separate queries then it would be nice to have them all called in one stored procedure (or sub-stored procedures).

Upvotes: 5

Adeel
Adeel

Reputation: 19228

UNION ALL is faster than UNION.

In case of UNION, if you have 10 columns and 100 rows, it will compare each cell (i.e. 10*100) to get distinct values, while in UNION ALL this is not the case. So the cost of getting distinct is high.

Upvotes: 6

AdaTheDev
AdaTheDev

Reputation: 147224

Executing them all as one query (UNION ALL'd together) would involve 1 roundtrip to the database as opposed to 16 roundtrips - as each roundtrip from your .NET code to SQL incurs an overhead, then obviously rationalising down to 1 could be beneficial from that perspective.

There are other perspectives though, depending on exact scenario e.g.
- if your queries are all pretty meaty, then it could become a bit harder to maintain (massive query of UNIONing lots of complex statements)
- if the individual queries are needed on their own (i.e. you don't always want to return them as part of one resultset), then you'd want to separate each query into it's own sproc (can't UNION sprocs)

So then how you combine those results into one call would change (e.g. temp table approach as already suggested). But in general, minimising round trips to the database server can reduce time. The flip side to ask yourself is: are there other areas of that could be tuned to give greater returns in terms of performance?

Upvotes: 1

RobinG
RobinG

Reputation: 196

If the underlying query results in a scan operation over an object, that operation will only need to be performed once for a UNION, but will be performed once for every query if not a UNION - so, yes, it probably will be faster.

Also, there will be a lot less communication overhead and chatter with a single query,

Upvotes: 1

Vladimir Tarasov
Vladimir Tarasov

Reputation: 631

You'd better to use stored procedure and a temporary table and insert all of you 16 queries results in temporary table. And return result back from this temporary table.

Upvotes: 3

Related Questions