SQL Case statement VS Union All performance

Question

I have a query that needs to incorporate conditional logic. There are 4 cases that need to be considered, and the resulting sets are disjoint.

I can implement the query using either a single SELECT and CASE/WHEN statements or using multiple SELECT statements and UNION ALL.

In general, is one of these implementations likely to be faster than the other? If so, why?

Answer 1

A Union does that many of selects together so a case when will be better generally IMHO if the from statement is not that complex and all other things are being equal. But they are NOT similar sql results:

A 'Case when ...' will add another horizontal row and by default a union of a select must have that amount of columns in the set being union'd so it will add more rows. For instance if you queried three separate tables and then union'd them together you are doing three selects, however if you just did three case whens it would be efficient if you were querying one table. But you could be querying five. Without knowing the source the answer really is: 'it depends'.

I just set the ole 'set statistics time on' when doing quick timing of the SQL engine to see. People can argue semantics but the engine does not lie when it tells you what is going on. SQL 2005 and higher I believe also has the 'include actual execution plan' in the menu bar. It is a nice looking little three squares icon in the shape of an L with the L point being in the upper left. If you have something very complex and are getting really into fine tuning that is the tool of choice to examine what the engine is doing under the hood with your query.

Answer 2

This really depends entirely on what the logic and data you expect to be selecting from look like. If you're running this SELECT against huge datasets and the logic is fairly simple like WHEN Val Between A and B THEN C you'll probably get a little bit of an uplift putting the logic in your where clause and doing a UNION ALL but not a ton of difference. On a comparatively small data set, it might not make any difference at all. It also might depend on whether or not you see this code being set in stone, or subject to periodically change. UNION ALL will certainly be quite a few more lines of code, because you're basically writing the same query over and over with different WHERE clauses, but it also may be easier to read and maintain.