CASE statement vs UDF

Database
N/A

CASE statement vs UDF

Hi,

I'm a java developer and new to TD and looking for some input/advice.

We have a query where we are using "CASE". After rough profilling found this "CASE" part is taking more time compared to others. The query is working fine for smaller data sets but it is CPU timing out of larger datasets . How do I get around with the CPU timeout ?

Here is how my query looks something like below:

 

select 
t1.business_type, t1.role1, t1.class1, t1.sub1, count(1) as all_count,
SUM(CASE
--not in scope
when BITAND(CAST(t3.flags4 as BIGINT), CAST(2 as BIGINT)) <> 0 then 0
when BITAND(CAST(t3.flags4 as BIGINT), CAST(32 as BIGINT)) <> 0 then 0
when cast((t3.flag5/8.0) as bigint) mod 2 <> 0 then 0
when cast((t3.flag5/4096.0) as bigint) mod 2 <> 0 then 0
when cast((t3.flag5/134217728.0) as bigint) mod 2 <> 0 and cast((t3.flag5/1048576.0) as bigint) mod 2 = 0 then 0
else 1 end) AS interested_count,

SUM(CASE
--not in scope
when BITAND(CAST(t3.flags4 as BIGINT), CAST(2 as BIGINT)) <> 0 then 0
when BITAND(CAST(t3.flags4 as BIGINT), CAST(32 as BIGINT)) <> 0 then 0
when cast((t3.flag5/8.0) as bigint) mod 2 <> 0 then 0
when cast((t3.flag5/4096.0) as bigint) mod 2 <> 0 then 0
when cast((t3.flag5/134217728.0) as bigint) mod 2 <> 0 and cast((t3.flag5/1048576.0) as bigint) mod 2 = 0 then 0
else amt end) AS intersted_amount

FROM (
table1 t1
INNER JOIN table1 t2 ON ( t1.pmt_flow_key = t2.pmt_flow_key2 ) -- to get flow description
INNER JOIN table3 t3 ON (t3.trans_id = t2.pmt_txnid);

 

Tags (1)