|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2050
Location: USA
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1308
Location: Vilnius, Lithuania
|
|
|
|
| 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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2050
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3059
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
