Re: Where v. If in high hit rate tables

In article <822389526snz@boothbc.demon.co.uk>, paul sellars <paul@boothbc.demon.co.uk> says: > >Hello, >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. >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 This cursor will get every row in the table and must normally be slower than option 2 >2. Rather than: > declare cur1 cursor for > select f1 > from table1 > where f1 = <condition> > foreach cur1 into v1 > process > . > . > end foreach This cursor will only fetch rows where f1 = <condition> and I would normally expect it to be much faster than 1. However why not try each of these two selects in isql or dbaccess and first -set explain on - and look at the -cost- of each option. Each option will return the same result but you will be surprised by the difference in -cost- especially on large tables and if f1 is an indexed field. Showing the output of explain to your colleague might score some internal points :-)