Re: query performance

Fragmentation is the same... Indexes are all detached. We are using cooked files on a SAN so who know what the structure is... although I did re-create one of the main indexes in the same space as the 'good' database, but that didn't change anything. The SET EXPLAIN query plan looks the same. I did not use the FORCE option. I will try that - I have been using dostats, but it seems to complete very quickly so Im not positive it is doing what I want. I think there is a new version since I downloaded last, so I may try that as well. This is the Query: SELECT {++INDEX(VEHICLE_OWNER idx_vehownername)} SKIP 0 FIRST 60 vo_first_name ,vo_middle_name, vo_last_name, v_vin, v_veh_make , v_veh_model, v_veh_year ,vr_license_num,ad_county, ad_county_name FROM vehicle_owner, vehicle, outer vehicle_reg, outer address WHERE ((vo_owner_type='O' or vo_owner_type='E') AND (vo_last_name >= 'ANDERSON' AND (case when vo_first_name < 'ADAM' AND vo_last_name = 'ANDERSON' THEN 'f' ELSE 't' END)::boolean )) AND vo_veh_id=v_veh_id AND vo_veh_id=vr_veh_id AND vo_addr_set_id=ad_addr_set_id AND ad_addr_type='P' ORDER BY vo_last_name, vo_first_name, vo_middle_name On Tue, Feb 5, 2013 at 9:00 AM, Art Kagel <art.kagel@gmail.com> wrote: > Table and/or index fragmentation? Does one have attached indexes and the > other one detached? (Attached indexes will show up in the output from > dbschema -ss with the "IN TABLE" clause). Are the tables/indexes located> on different disk structures that may either be configured differently > under the hood or experiencing different levels of load from external > sources? Did you use the FORCE option when you updated statistics? If you > run the query with SET EXPLAIN are the query plans different? How? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > On Tue, Feb 5, 2013 at 10:55 AM, Laurie Gustin <lgustin@utah.gov> wrote: > >> I have two databases that appear to be identical on the same server. The >> data is only slightly different. I run the same query on both databases >> and on one it finishes in 2 seconds... the other takes over 4 hours. I >> have rebuilt indexes, and updated statistics but nothing seems to help. >> Is there anything else I can look at to see why the difference in >> performance? >> >> Thanks >> Laurie >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >> >