Hello
again!
I modified the
application I mentioned in my last post, the one that is taking 20 minutes to
solve a problem on our customer's system that is solved in under ten seconds on
my machine. The application is written in C++. All data access is
through a class named CCRecordset. We have derivatives of that class for
every table in our database. CCRecordset uses ADO to communicate with the
database, and the database connection is based on a DSN. The modification
to the CCRecordset class logs the queries, the time the query was issued, the
time the query returned, and the number of records returned.
The slow access
seems to be happening with a single table. Here's the
query:
select
coil_id,step_number,order_number,status,status_date,cycle,weight,width,gauge,outside_diam,inside_diam,heat_number,base,charge,stack,
stack_position,alpha_code,account,archived,bed,bundle_flag,bundle_id,customer,department,disposition,entered_by,entered_date,
final_cooling_hotspot,final_disposition,final_heating_coldspot,final_heating_hotspot,finish_code,footage,grade,
heating_coldspot_time_reached,heating_hotspot_time_reached,hydrogen,location,manual,next_operation,product,priority,
promised_date,promised_week,promised_year,reanneal,received,redeox,required_cooling_hotspot,required_heating_coldspot,
required_heating_hotspot,sand_seal,schedule,roll_sequence,updated_by,updated_date
from inventory
where status = (select status from coil_status where free=1)
and archived=0
and (coil_id='320787' or coil_id='949806' or coil_id='320830' or coil_id='183015' or coil_id='320647' or coil_id='987767')
order by coil_rating desc
stack_position,alpha_code,account,archived,bed,bundle_flag,bundle_id,customer,department,disposition,entered_by,entered_date,
final_cooling_hotspot,final_disposition,final_heating_coldspot,final_heating_hotspot,finish_code,footage,grade,
heating_coldspot_time_reached,heating_hotspot_time_reached,hydrogen,location,manual,next_operation,product,priority,
promised_date,promised_week,promised_year,reanneal,received,redeox,required_cooling_hotspot,required_heating_coldspot,
required_heating_hotspot,sand_seal,schedule,roll_sequence,updated_by,updated_date
from inventory
where status = (select status from coil_status where free=1)
and archived=0
and (coil_id='320787' or coil_id='949806' or coil_id='320830' or coil_id='183015' or coil_id='320647' or coil_id='987767')
order by coil_rating desc
None of the six
coils are free, so this query returns 0 records. There are indexes on the
coil_id and coil_rating fields. There are just over 100,000 records in
this table. The coil_rating field is null for all but about 1200 of
them. This query took about 75 seconds.
I ran PGAdmin on the
same machine that the application is running on, and executed the same query on
the same database. It took about 30 milliseconds.
I could well believe
that the cursor location is set incorrectly for the inventory table object, but
I would expect that it would be set the same for this object as for all other
CCRecordset objects, and data access would be just as slow for all of
them. Instead, this table seems to be the only one affected. A query
on another table with just under 1700 records that returns all records in the
table took about one second.
Can anyone suggest
why querying this one table is taking so much time?
Thanks
again!
Rob
