Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-25-2016
02:46 PM

07-25-2016
02:46 PM

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);