Performace of CASE statement

V S Amarendra Reddy · Active User Joined: 13 Sep 2006 Posts: 216 Location: USA

Hi

I need to select some rows with timestamp greater than or equal to current timestamp when a host variable is 'A' or 'F' and if the host variable is 'C' then select rows with timestamp equal to a timestamp given in the input. I ahve clubbed both of them into a single query using a CASE statement .

I would like to know if this is performace effective or defective.

dbzTHEdinosauer · Posted: Wed Jul 07, 2010 8:47 pm

have you tested this?

I would think that you would want your
first else to be a 3999-12-31-23.59.59.999999

actually,
since I don't think your select will return what you want,
I would write it:

dbzTHEdinosauer · Posted: Thu Jul 08, 2010 11:25 am

The more I thought about this,
since the deciding factor is the value of a host variable,
you should have two simple sql's.

one for ts_endval_308racm >= Current_timestamp
when an IF statement finds ws-co-act = 'A' or 'F'

and
one for ts_endval_308racm = = :ts-endval-308rnge
when an IF statement finds ws-co-act = 'C'

There is no need to add the extra burden on db2
to check the value of a host variable,
because of the desire to have only one sql.

V S Amarendra Reddy · Active User Joined: 13 Sep 2006 Posts: 216 Location: USA

Hi Dick Brenholtz

I had little mistake in that query that I should use AND instead of OR. Now I am getting the desired result.

Can you please tell me which one is performace effective the CASE or the one you ahve given.

I have gone through the performance tuning of DB2, they mentioned that avoid using STAGE2 predicates(i.e. testing expression = value in this case :ws-co-act= some value).

But at the same time they mentioned that if you have the expression thing on the right side of the column it's fine.

So I am bit confused.

Kindly help.

V S Amarendra Reddy · Active User Joined: 13 Sep 2006 Posts: 216 Location: USA

I am sorry for the previous post..You were correct my query doesn't work. So I changed it as explained by you.

Thanks a lot for the help.

I didn't see your second post before I have posted the query. Yeah before posting the question I have designed 2 programs with single query and another qith multiple queries(to use it if I can't use first one).

But unfortunately these 2 are not just singleton SELECTs they are cursors.So still you think that I should use the second way of programming??

dbzTHEdinosauer · Posted: Thu Jul 08, 2010 1:38 pm

V S Amarendra Reddy,

ok, my philosophy:

db2 is a resource shared by everybody

my program runs in its own space.

why offload a compare of a literal to a working-storage location to db2?
the less I require of db2, the more db2 for others.

Only difference I see between singleton selects and cursors is the number of rows returned.

sure that means you have to keep track of which open/fetch/close to use,
and have a functional error routine which will provide adequate info, in case it is needed.

short answer to your question - regardless of singleton select or cursor,
do as much in the program as possible-
with the exclusion of ISO functions, date/time compares/manipulation
db2 does that better.

yeah I know. is there a hard and fast rule?
no, it is one of those things that you know when you see it,
but very difficult to 'nail-down'.
there will always be things that I consider an exception, and have db2 do,
but comparing working-storage variables to literals, I rarely do - only in really complex where clauses.

i hope that I did not confuse you.

sushanth bobby · Posted: Thu Jul 08, 2010 7:38 pm

Dbz,

V S Amarendra Reddy · Active User Joined: 13 Sep 2006 Posts: 216 Location: USA

Hi dbzTHEdinosauer,

Firstly Thanks a ton for the response given. I am so clear. I am going to use the second way of using 2 cursors to increase the performance as told by you.

Thank you once again.

Regards
Amar

dbzTHEdinosauer · Posted: Thu Jul 08, 2010 8:10 pm

glad somebody appreciates my ramblings.

thx