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.
Post by Laurie GustinFragmentation 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.
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
Post by Art KagelTable 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.
Post by Laurie GustinI 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
http://www.iiug.org/mailman/listinfo/informix-list