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,g
auge,outside_diam,inside_diam,heat_number,base,charge,stack,
stack_position,alpha_code,account,archived,bed,bundle_flag,bundle_id,cus
tomer,department,disposition,entered_by,entered_date,
final_cooling_hotspot,final_disposition,final_heating_coldspot,final_hea
ting_hotspot,finish_code,footage,grade,
heating_coldspot_time_reached,heating_hotspot_time_reached,hydrogen,loca
tion,manual,next_operation,product,priority,
promised_date,promised_week,promised_year,reanneal,received,redeox,requi
red_cooling_hotspot,required_heating_coldspot,
required_heating_hotspot,sand_seal,schedule,roll_sequence,updated_by,upd
ated_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