SQL simplifying case in case in case

Question

I got the following SQL code (part of a select Statement):

Case
      When HilfsTab2.Gruppe = 'HST' And Basis.Breite_FLA = Basis.Breite Then 0
      Else Case When HilfsTab2.Gruppe = 'SA' Or HilfsTab2.Gruppe = 'HO / TB' Or
        HilfsTab2.Gruppe = 'PR' Then 0 Else Case
          When HilfsTab2.Gruppe Is Null Then -1 Else 1 End End
    End As IsHST_Fluegel

Now, I run this over a table of several million entries. From my understanding, SQL checks the first case when for all rows, then the second for all entries and so on. This takes ages. Now I was thinking, there needs to be an easier way to do this.

I was thinking of a stored procedure / custom function that basically outputs -1, 0 or 1 depending on the entry.

Thanks in advance

Tuning highly depends on the DBMS. Which DBMS are you using? Oracle? Postgres? — , Sep 24 '13 at 11:40

score 1 · Answer 1 · answered Sep 24 '13 at 11:44

1

For a possible speed improvement, do the NULL check first, the column comparison last and refactor to remove the nested CASE:

CASE WHEN HilfsTab2.Gruppe IS NULL
     THEN -1
     WHEN HilfsTab2.Gruppe IN ('SA', 'HO / TB', 'PR')
       OR (HilfsTab2.Gruppe = 'HST' AND Basis.Breite_FLA = Basis.Breite)
     THEN 0
     ELSE 1
END AS IsHST_Fluegel

answered Sep 24 '13 at 11:44

Steve Chambers

37,270
24
156
208

The order of the CASE items does (should?) not matter. SQL does not (and may not) short circuit boolean expression. I don't know whether there are DBMS that work differently though. – Sep 24 '13 at 11:46
Good point! I don't actually know the answer to that either - had assumed short circuiting would be done and [this answer](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated#12320806) seems to indicate it may be but haven't found any hard evidence and as you say may be DBMS-dependent... simplest way may be just for @Spurious to try it and report back on whether it makes any difference. – Steve Chambers Sep 24 '13 at 11:56
I got a question, if I would create a procedure doing the Case When on it's own, would this possibly increase the speed? – Spurious Sep 24 '13 at 14:30
Do you mean a UDF (User Defined Function)? I doubt if that would increase the speed - if anything it might have the opposite effect. Is there anything else in the query that could affect the speed? – Steve Chambers Sep 24 '13 at 14:38
I will have to check, if there is. It's quite a complex query, so this might not be the only problem (if it's one). – Spurious Sep 24 '13 at 15:00

score 0 · Answer 2 · answered Sep 24 '13 at 11:38

Your case could be simplified as:

Case
      When HilfsTab2.Gruppe = 'HST' And Basis.Breite_FLA = Basis.Breite Then 0
      When HilfsTab2.Gruppe in ('SA', 'HO / TB', 'PR') Then 0
      When HilfsTab2.Gruppe Is Null Then -1
      Else 1
End As IsHST_Fluegel

But this will not speed up your query. If you want to select millions of rows, it would take time anyway.

SQL simplifying case in case in case

2 Answers2