Re: Where v. If in high hit rate tables

On Jan 23, 9:32am, paul sellars wrote: > Subject: Where v. If in high hit rate tables > 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. > > We cannot see any advantage method 1 has over method 2. > Anybody got any ideas on this? > >-- End of excerpt from paul sellars Generally speaking there is no advantage to not using the where clause. In practice though, there are rare occasions where it may be true that moving the selection clause out of the where clause might improve performance. This generally only occurs under the following set of fairly unusual circumstances. 1) You are hitting almost all the rows in the table. 2) Including the where clause causes the optimiser to use an index rather than a sequential scan and that index is not clustered. 3) The overhead of scanning the index and then doing random access to the data is higher than the cost of sequentially scanning the table and ignoring the rows not required. 4) No order by is required. If an order by is required then either an index and filter will be used or temporary files will be built and sorted. In either case excluding the unwanted rows within the engine as early on in the process as possible is always cheaper than leaving them in the result set. 5) The program is not running client/server as generally the network cost of the transferring the unwanted rows to the client is higher than the extra cost of using the index scan. Though again with a high enough hit rate even this extra cost might be worth it to avoid the index scan. With each new Informix release, as the optimiser and statistics are improved, this kind of occurence becomes rarer. This is because the optimiser itself can make the choice that a sequential scan of the entire table will be faster than the index scan. If the optimiser makes that choice then it is always faster for the engine to discard rows that do not fit your criteria as you avoid the communication and processing overhead incurred by your program discarding rows that are not required. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!