IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Two where-criteria with GT - Performance?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)

PostPosted: Wed Jun 12, 2024 2:16 pm
Reply with quote

Hello everybody,

I have a table with two key (and some more non-key) attributes that I'd like to select / filter by the greater operation.
Maybe there are some more solutions but let's just talk about two of them:
First:
Code:
WHERE      key_1  >  :host-key-1
   OR (    key_1  =  :host-key-1
       AND key_2  >  :host-key-2)

Second:
Code:
WHERE         key_1||      key_2
      > :host-key-1||:host-key-2

Remark 1: Both key cols are character /w fixed length and not nullable.
Remark 2: Db2 z/OS, v13...

Think, there are no doubts both variants work the same.

But my question is:
Has anybody of you an idea which of both is more performant?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2081
Location: USA

PostPosted: Wed Jun 12, 2024 4:21 pm
Reply with quote

1) A concatenation operation consumes more resources than a compare for the same strings.

2) Two concatenations need to be done for every record, while number of compares varies from 1 to 3 per record. The average might be 1.5 ops/rec, or less.

Compare with logical selection should be better from performance point of view.
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1308
Location: Vilnius, Lithuania

PostPosted: Wed Jun 12, 2024 4:34 pm
Reply with quote

sergeyken wrote:
2) Two concatenations need to be done for every record, while number of compares varies from 1 to 3 per record. The average might be 1.5 ops/rec, or less.

If the optimizer is smart enough, the result of the first compare will also be used for the second compare, making the concatenation come out even worse! That's what I do on x86.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2081
Location: USA

PostPosted: Wed Jun 12, 2024 6:15 pm
Reply with quote

prino wrote:
sergeyken wrote:
2) Two concatenations need to be done for every record, while number of compares varies from 1 to 3 per record. The average might be 1.5 ops/rec, or less.

If the optimizer is smart enough, the result of the first compare will also be used for the second compare, making the concatenation come out even worse! That's what I do on x86.

Yes, this is what I also meant when estimating as 1.5 or less ops/rec.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3066
Location: NYC,USA

PostPosted: Thu Jun 13, 2024 4:09 pm
Reply with quote

It all depends .. how much is underlying data in those tables ?
Have you done the EXPLAIN yet to make sure index scans are done as OR mostly abandon the index scan ..

Though it may not always prefered using unions but try union all split after OR as a separate UNION .

As long as you make sure index scans are done then You are good to go 😊.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Which SORT utility can improve the Pe... DFSORT/ICETOOL 16
No new posts COBOL Performance Tuning COBOL Programming 6
No new posts Sum based on criteria for each id, ea... COBOL Programming 1
Search our Forums:

Back to Top