SQL Case statement VS Union All performance

Question

I have a query that should include conditional logic. There are 4 cases that need to be taken into account, and the resulting sets do not intersect.

I can implement a query using one SELECT and CASE / WHEN SELECT or using several SELECT and UNION ALL SELECT .

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

Answer 1

Union makes it so that many of them are selected together, so the case when it would be better in general IMHO if the from operator is not so complicated and all other things are equal. But they are NOT similar sql results:

A "Case when ..." will add another horizontal row, and by default the join of the select element should have as many columns in the set as union'd, so it will add more rows. For example, if you query three separate tables and then combine them together, you make three choices, however, if you just made three cases where it would be effective if you would query one table. But you can request five. Without knowing the source, the answer is actually: "it depends."

I just set the ole 'statistics setup time when you make a quick timing for the SQL engine. People may argue about semantics, but the engine does not lie when it tells you what is happening. SQL 2005 and above, I believe, also has "include the actual execution plan" in the menu bar. This is a beautiful-looking small three-dimensional icon in the shape of an L with a dot L located in the upper left corner. If you have something very complicated, and you really go into the fine-tuning, which is the tool of choice for exploring what the engine does under the hood with your request.

Answer 2

It really depends entirely on how the logic and data you expect from the selection look. If you use this SELECT for huge datasets, and the logic is pretty simple, like WHEN Val between A and B THEN C, you will probably get a bit of a raise by entering the logic in your where clause and making UNION ALL, but not a ton of the difference. On a relatively small data set, this may not matter. It may also depend on whether you see this code in stone or change it periodically. UNION ALL will certainly have a few more lines of code, because you basically write the same query over and over with different WHERE clauses, but it can also be easier to read and maintain.