Reading ODBC table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm reading an ODBC data source using DAO and the machine is really getting
bogged down after just a few minutes. Can anyone offer any suggestions as to
how I can improve the efficiency of this process since there are several
million records the code has to read through and I can hardly do anything
else on the machine because it's running so slow. Also, the application gets
to a point where it stops responding at all using nearly 100% of the CPU time.

Thanks in advance,
 
Glenn said:
I'm reading an ODBC data source using DAO and the machine is really
getting bogged down after just a few minutes. Can anyone offer any
suggestions as to how I can improve the efficiency of this process
since there are several million records the code has to read through
and I can hardly do anything else on the machine because it's running
so slow. Also, the application gets to a point where it stops
responding at all using nearly 100% of the CPU time.

Thanks in advance,
Click to expand...

What are you doing in your DAO code?

Manipulating records with RecordSets should be rare and only for small sets
of data. If what you are doing could be accomplished with SQL it would
likely be a LOT faster.
 
Glenn Suggs said:
I'm reading an ODBC data source using DAO and the machine is really
getting
bogged down after just a few minutes. Can anyone offer any suggestions as
to
how I can improve the efficiency of this process since there are several
million records the code has to read through and I can hardly do anything
else on the machine because it's running so slow. Also, the application
gets
to a point where it stops responding at all using nearly 100% of the CPU
time.

Thanks in advance,
Click to expand...

Are you sending in a multijoin query? [Especially if the query has
subqueries]
 
Thanks Rick,
After setting up an append query in design view, the job took 25 minutes to
extract the data, as opposed to 13 hours and 40 minutes by using code to read
one record at a time and appending records as necessary. I thought I
remembered the DAO code working better and maybe it does in certain cases but
it certainly didn't here.

Thanks again,
 
Back
Top