Re: query performance

Laurie: So to apply FORCE in dostats add the --force-run option -- only necessary if you are using 11.70 and AUTO_STAT_MODE is enabled (1) in your ONCONFIG file. By fragmentation, I meant the number of extents in each table on one server versus the other. Also, since you are using COOKED files, are you using a journaled filesystem for the chunks? Some journaled filesystems are COPY-ON-WRITE which essentially fragments your table more and more over time, moving each page to a different location on disk every time it is written to. If you are not using 11.70, the engine can't use multi-index scans, so this version of the query may run faster depending on what columns are indexed in what order: 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' 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' UNION 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='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 I haven't tried this, you may have to make this a derived table query in the FROM clause of a SELECT SKIP 0 FIRST 60 * FROM (<query here>); in order for it to correctly process the SKIP & FIRST clauses. Of course, that doesn't explain different performance behavior between the two databases. 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 11:08 AM, 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 >>> >>> >> >