Query with Many Case Statements - Optimization

Query with many CASE statements - optimization

To me this looks like a botched attempt in sub-typing. This is what I think you have now.

Based on the model, the following should work:

;
with
q_00 as (
    select
         pa.Deal_Id                                                             as Deal_Id
       , coalesce(sp.BROJ_TIKETA, fw.BROJ_TIKETA, sw.tiket, ia.BROJ_TIKETA, '') as Ticket_No
       , coalesce(sp.Trans_Id, fw.Trans_Id, sw.Trans_Id, ia.Trans_Id)           as Trans_Id
    from #PotencijalniAktuelni as pa
    left join kplus_sp         as sp on sp.sp_Id = pa.Deal_Id and pa.KplusTable_Id = 1
    left join kplus_fw         as fw on fw.fw_Id = pa.Deal_Id and pa.KplusTable_Id = 2        
    left join dev_sw           as sw on sw.sw_Id = pa.Deal_Id and pa.KplusTable_Id = 3
    left join kplus_ia         as ia on ia.ia_Id = pa.Deal_Id and pa.KplusTable_Id = 4
)
select
      Deal_Id
    , max(Trans_Id) as TransId_CurrentMax
into #MaxRazlicitOdNull
from  q_00
where Ticket_No <> ''
group by Deal_Id ;

SQL Server 2005 +

SQL: Optimize query with multiple CASE statements

maybe this help

with tabletemp as (
    SELECT z.contract_number
    , COUNT(DISTINCT damagenumber) damagenumber_count
    FROM table z
    WHERE date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate AND internalname <> 'CONDITION'
    GROUP BY z.contract_number 
), tabletemp2 as (
    SELECT y.contract_number
    , SUM(y.payment) payment_sum
    FROM table_y y
    WHERE date BETWEEN add_months(trunc(sysdate), - 6) AND sysdate AND internalname <> 'CONDITION'
    group by y.contract_number
)

SELECT
    contract_number,
    product_description,
    product,
    CASE WHEN ( x.produkt = 'product_name' AND tt.damagenumber_count = 1 AND tt2.payment_sum > 1500 ) THEN
            tt.damagenumber_count
            ELSE
            0
            END AS count_numbers,
FROM
    table_a x
    join tabletemp tt on (tt.contract_number = x.contract_number)
    join tabletemp2 tt2 on (tt2.contract_number = x.contract_number)

Maybe this query have some errors (I can't test) but you should try this way

How to optimize SELECT query with multiple CASE statements?

lateral and distinct on (IMO) contribute for readability. distinct on will also have an impact on performance although I can't guess if positive or negative.

select
    g.gid,
    extract(epoch from g.created)::int created,
    extract(epoch from g.finished)::int finished,
    g.letters,
    g.values,
    g.bid,
    m.tiles,
    m.score,
    r.*
from
    words_games g
    left join (
        select distinct on (gid, played) *
        from words_moves
        order by gid, played desc
    ) words_moves m on m.gid = g.gid
    left join (
        select distinct on (uid, stamp) *
        from words_social
        order by uid, stamp desc
    ) words_social s1 on s1.uid = g.player1
    left join (
        select distinct on (uid, stamp) *
        from words_social
        order by uid, stamp desc
    ) words_social s2 on s2.uid = g.player2
    cross join lateral (
        select
            g.player1, g.player2,
            extract(epoch from g.player1)::int, extract(epoch from g.player2)::int,
            array_to_string(g.hand1, ''),
            regexp_replace(array_to_string(g.hand2, ''), '.', '?', 'g'),
            g.score1, g.score2,
            s1.female, s2.female,
            s1.given, s2.given,
            s1.photo, s2.photo,
            s1.place, s2.place
        where g.player1 = in_uid
        union all
        select
            g.player2, g.player1,
            extract(epoch from g.player2)::int, extract(epoch from g.player1)::int,
            array_to_string(g.hand2, ''),
            regexp_replace(array_to_string(g.hand1, ''), '.', '?', 'g'),
            g.score2, g.score1,
            s2.female, s1.female,
            s2.given, s1.given,
            s2.photo, s1.photo,
            s2.place, s1.place
        where g.player1 != in_uid
    ) r
where
    in_uid in (g.player1, g.player2)
    and (g.finished is null or g.finished > current_timestamp - interval '1 day')

SQL - What is the performance impact of having multiple CASE statements in SELECT - Teradata

The case statements are going to be much less of a factor than the joins in the WHERE clause.

The main driver of performance in SQL is I/O -- reading the data from disk. I think of it as two orders of magnitude more important than the processing going on in rows. This is just a heuristic, not based on specific tests on a database.

You are doing self-joins, which will require either lots of work reading the table or a fair amount of work dealing with indexes.

The case statement, on the other hand, gets turned into very primitive hardware commands -- equals, gotos, and the like. The data resides in memory closest to the processors, so it is going to zip along. You are doing nothing fancy in the case statement (such as a like or a subquery). I would imagine that the query would be just as fast if you removed most of the lines in the statement.

If you are having issues with performance, put an index on (VERS_NM, RPT_PERD_TYPE_CD, DATA_VLDTN_IND, Perd_END_RPT_DT). This four-part index should allow you to get the max date without invoking I/O requests on the original table.

Optimize query with multiple OR conditions

@dale-k's answer in the comment just solved my performance problem.

The solution to multiple OR bad performances is to use UNION ALL. my execution time dropped to 60-90ms from 700+ms.

---

Related Topics