Re: Search by index vs. sequential scan

Robin Munn wrote: > Mark D. Stock <mdstock@mydassolutions.com> wrote: > >>Robin Munn wrote: >> >>>Ladies and gentlemen, >>> >>>I've been running into a very interesting problem, and I'd appreciate >>>your advice. When is it better to have a sequential scan vs. using an >>>index in a query? To be specific, if we change the OPTCOMPIND parameter >>>in /usr/informix/etc/onconfig from 2 to 0 (thereby telling the optimizer >>>to prefer indexes over sequential scans), what kinds of queries are >>>likely to suffer performance penalties? >> >>First, the optimiser will rely on you supplying up to date statistics by >>running UPDATE STATISTICS regularly. >> >>Second, when the optimiser believes you are going to read the majority >>of the rows in a table, then a sequential scan is going to be more >>efficient that reading indexes first. If you are going to read a small >>percentage of rows in the table, then an index read is going to be more >>efficient. >> >>The recommendation you saw for setting OPTCOMPIND to 0 is for OLTP type >>systems that generally read small percentages of tables. Setting >>OPTCOMPIND to 2 would be beneficial to batch type processing and DSS >>queries. >> >>If you find a particular query suffering from your global setting, then >>you can always override that value by setting the environment variable. > > > Thanks; that's very helpful. Most of our queries tend to pick out a > single row (or a few rows) from a table, so OPTCOMPIND of 0 does make > sense for us. And given that it took care of the locking problem I > described in my earlier post, I think we'll keep it. > > Thanks also for the environment variables suggestion. Do you mean > something like this? > > bash$ OPTCOMPIND=2 cat batch_query.sql | dbaccess Yes. Don't forget to export it though, dbaccess recognises the .sql suffix, and what's that cat for?: OPTCOMPIND=2 export OPTCOMPIND; dbaccess - batch_query; unset OPTCOMPIND Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+