Where v. If in high hit rate tables
>The following was suggested to one of my colleagues:
>Using I4GL to get rows from a large table with a high hit rate, there
>is a performance advantage in moving the selection criteria from the
>where clause to an if statement inside the loop.
Wrong! If you let the server exclude the unwanted rows then they are
not transferred to the client therefore the communicate overhead
between
the client and the server is lower. Also because there is less
communication there will be less context switching on the machine
which runs the server.
For example,
1. Use:
declare cur1 cursor for
select f1
from table1
foreach cur1 into v1
if v1 = <condition> then
process
.
.
else
continue foreach
end if
end foreach
2. Rather than:
declare cur1 cursor for
select f1
from table1
where f1 = <condition>
foreach cur1 into v1
process
.
.
end foreach
We cannot see any advantage method 1 has over method 2.
The only way that 1 could be better than 2 is it the number of rows
selected in a hit percent of the number of rows in the table and
method
2 used an index to access the data rows whereas method 1 used a
sequential scan i.e.
Method 1 reads data + index
Method 2 reads data
In that case the extra disk I/O overhead involved in reading the
index
swaps the overhead for the communication between the client and the
server. In that case either drop the index which Method 1 uses
(assuming it is not needs for something else) or change the SQL
for Method 1 by appending something like (1=1) OR (1=1) to the
query
as queries containing OR's do not use indices.