Re: a slow query selection.

On Fri, 19 Feb 1999, Thai Nguyen wrote: >I am working on a selection by using ESQLC to popup some info >from a table for a number of selected accounts. Here is my select statement: > >select acctno, detail_num from detail_table >where acctno in ("n1, n2, n3, n4, ....nn") Hmmm. Do you really write that, or do you write: SELECT acctno, detail_num FROM detail_table WHERE acctno IN ("n1", "n2", "n3", "n4", ..., "nn") The first version won't match any rows, of course, since the string given is longer than any acctno in the table, at least in the example you outline. Now, whether the optimizer knows that or not is a different issue. >where: > n1-nn: my selected account number > > Column name Type > detail_num char(10) > acctno char(10) > > index1- unique - detail_num, acctno > index2- dupls - acctno > >The query selection run very slow even when there are only >821,026 records in the table. So it's probably using a sequential scan. Did you run UPDATE STATISTICS yet? Did you do it correctly for 7.3 -- the rules may be different from those for 7.2 and other versions. What does SET EXPLAIN have to say? Is it using either index? How many account numbers are you normally listing in the IN clause? The unique index is not going to be usable; your query condition does not specify any detail_num values, so an index starting with detail_num cannot help. Have you tried writing the WHERE clause as multiple OR conditions -- it shouldn't be faster, but it might use a different query plan. Have you tried inserting the IN values into a temp table and then doing a join with the temp table? You might need to create an index on the temp table, and you might want to run update statistics on the table. Again; I wouldn't expect it to be faster, just different. Again, SET EXPLAIN will help you determine what is going on. How many detail_nums are there per acctno? >Could anyone point me out a better way in to get the same result with a >higher performance ??? My Informix is 7.3. Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn