0

I want to reduce the size of my code and have a query that runs as fast as possible.

I have one CASE expression copy-pasted multiple times within the same SELECT. This is redundant and may run the switch multiple times instead of once, making it slower than it should (Or doesn't it? Does the SQL graph detect the redundant code and reuse results?)

-- My view.
SELECT
    /* 20 lines case expression */ + 1 AS a,
    /* 20 lines case expression */ + 2 AS b,
    /* 20 lines case expression */ + 3 AS c,
    /* 20 lines case expression */ + 4 AS d,
FROM some_table

To avoid repeating myself I would like to do the following:

-- The following code is not valid. It is meant to represent my goal.
SELECT @temp := /* 20 lines case expression */ AS complex
FROM some_table;

SELECT
    @temp + 1 AS a,
    @temp + 2 AS b,
    @temp + 3 AS c,
    @temp + 4 AS d,
FROM some_table

But as you may know, user-defined variables are not a thing with views. Also, CTEs are often described as being slow (To which extent and in which situations I do not know).

I would like to use a view alone, is it possible? (speed is more important) If not, then it's fine, I'll just do one of the following.

  • Just keep the copy/pasted code
  • Use a stored procedure for the case expression (adding to the many procedures cluttering the database)
  • Simply using a CTE (because in this case it may be fast?)

Thanks for your help and for sharing your approach to this problem.

UPDATE:

On your demands, here is a more practical example of what I'm dealing with. It's based on legacy code, please do not credit me for it.

-- It's a simplified example with small alterations.
SELECT
    -- Nothing special ...
    [col_x] AS X,
    [col_y] AS Y,
    [col_z] AS Z,
    -- Then there is this column...
    CASE
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'First thing to compare with'
        THEN [return_First],
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'Second thing to compare with'
        THEN [return_Second],
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'Second thing to compare with'
        THEN [return_Third]
    END AS [Monster Case Expression That Makes Me Cry]
FROM some_table
Florian Fasmeyer
  • 795
  • 5
  • 18
  • 2
    Going to dump the same comment that I put in your [previous question](https://stackoverflow.com/q/66706367/2029983) before you immediately deleted it: *" A `VIEW` can't have variables. If you don't want to repeat a specific expression then define it in a CTE or `VALUES` construct."* – Thom A Mar 19 '21 at 10:44
  • Also, the comment in your code says *"20 lines switch case-statement"*; T-SQL doesn't support `Switch`/`Case` statements. – Thom A Mar 19 '21 at 10:45
  • @Larnu This question is for Microsoft sql-server. I run Switch Cases on it just fine. ;) – Florian Fasmeyer Mar 19 '21 at 10:49
  • No, you don't. Again, T-SQL does **not** support `Switch` statements. Never has done. – Thom A Mar 19 '21 at 10:50
  • @Larnu I am not using T-SQL. I am on Microsoft sql-server. I am using Microsoft SQL Server Management Studio. And I am working in a very very old database with many statements following this pattern (just in case there is a misunderstanding) CASE SWITCH ... = ... THEN 'return_this' ELSE 'return_that' END – Florian Fasmeyer Mar 19 '21 at 10:54
  • SQL Server uses T-SQL, Florian... T-SQL (Transact-SQL) is the specific dialect that Microsoft SQL Server and SyBase uses. The only `CASE` that T-SQL supports is a [`CASE`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15) **expression** and there is no `SWITCH` expression in T-sQL. – Thom A Mar 19 '21 at 10:55
  • I suspect you are *not* really using SQL Server. Without the actual code, it is impossible to identify the RDBMS. The fact you have `SELECT @temp :=` also indicates it is not T-SQL.# – Thom A Mar 19 '21 at 11:00
  • 4
    *"I am not mad! I am healthy and alive! "* That is a `CASE` **expression** Florian, not a `Case`/`Switch` statement. I can repeat this till I go blue, as it's true (at the time of writing): **T-SQL does not support `Case`/`Switch` statements.** – Thom A Mar 19 '21 at 11:01
  • Understood. I will modify my question. CASE expression instead of CASE-Statement. Makes sense. Thanks for correcting it. – Florian Fasmeyer Mar 19 '21 at 11:06
  • 2
    Either way, like I mentioend at the start, I would still use a CTE or `VALUES` table construct. Without actual meaningful code, this is difficult to demonstrate. I suggest, perhaps, looking at the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15#c-specifying-multiple-values-as-a-derived-table-in-a-from-clause) and then ask if you don't understand; with your attempts or description of what you don't understand. – Thom A Mar 19 '21 at 11:08
  • 1
    Just create your query like `select ... from (select ..) x` - you can evaluate your complex epressions in inner query and use its results in outer one. – Arvo Mar 19 '21 at 11:10
  • 1
    Please show your actual code, `CROSS APPLY(VALUES` should do the trick. And CTEs are not slow, or any slower than a view, it just depends how you use them – Charlieface Mar 19 '21 at 11:12
  • @Larnu Sorry for the confusion! Now, my worry is that a CTE would be very slow compared to a view. While I will make one and test it right now, I need to know if the CTEs reputation to be slow is due to poor use by new developers or due to some kind of processes not being adapted for CTEs. btw +2 ups for your patience. Thanks! ;) – Florian Fasmeyer Mar 19 '21 at 11:13
  • @Charlieface Ok! I'll complete my working example. – Florian Fasmeyer Mar 19 '21 at 11:16
  • 3
    "CTEs are known to be extremely slow.". Only in the same way that queries are slow, stored procedures are slow, views are slow, etc. They are not, in and off themselves a "do things dumb now" switch. Rather than presuming slowness, use one and *measure* performance, and if it's adequate, use it. – Damien_The_Unbeliever Mar 19 '21 at 11:17

2 Answers2

2

The best way to do this is to put the expression in an APPLY, which means you can re-use it anywhere in the query:

SELECT
    v1.Temp + 1 AS a,
    v1.Temp + 2 AS b,
    v1.Temp + 3 AS c,
    v1.Temp + 4 AS d,
FROM some_table
CROSS APPLY (VALUES (
    CASE WHEN /* 20 lines case expression */
    END
) ) AS v1(Temp)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
2

It's as simple as:

SELECT 
    CaseColumn + 1 AS a,
    CaseColumn + 2 AS b,
    CaseColumn + 3 AS c,
    CaseColumn + 4 AS d
FROM (
    SELECT
        /* 20 lines case expression */ AS CaseColumn
    FROM some_table
)
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • This is a very good answer, so I gave you +1. But Charlieface's answer is even more useful to me. Thanks and thanks again! You're awesome! :) – Florian Fasmeyer Mar 19 '21 at 13:09