Re: query performance

Sounds interesting... we'd need: - confirmation that the Informix version is the same - differences in $ONCONFIG, particularly OPT_GOAL - How is data different? Different "age"? - Query plans No SAN issues would explain 2s against 4H (none I've heard about until now at least) On the other hand, if one query is using and index that doesn't require the SORT phase, and since you just want 60 records, that would explain a lot... Regards On Tue, Feb 5, 2013 at 4:08 PM, Laurie Gustin <lgustin@utah.gov> wrote: > 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 >>> >>> >> > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...