Re: query performance

Thanks all - I had missed something in the sqexplain - I found the table at fault... the indexes were the same, but knowing where the error was, I was able to build one more index and the query runs super fast now. I did learn a bunch from your questions :) THANKS!!! Laurie On Tue, Feb 5, 2013 at 9:25 AM, Fernando Nunes <domusonline@gmail.com>wrote: > 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... >