How to improve the performance of the INSERT execution?

[XR]

Hi,all,
it's too slow to insert record into mdb when I use ADO to access the DB.

Is there any way to improve it?

Thx a lot.

 

[Dirk Goldgar]

Hi,all,
it's too slow to insert record into mdb when I use ADO to access the
DB.

Is there any way to improve it?

Thx a lot.

How are you inserting the record? Are you inserting a record in the
same database in which the code is running? You can expect DAO to be
faster than ADO for an .mdb file, and with either object library,
executing an append query will be faster than opening a recordset just
to add a record. For example,

CurrentDb.Execute _
"INSERT INTO MyTable (FieldA, FieldB) " & _
"VALUES (123, 'ABC')", _
dbFailOnError

If you post the code you're currently using, maybe we can come up with a
more specific suggestion.

 

[XR]

Thanks Dirk.
I use ado recordset to open an existed table then Edit then call AddNew then
Update for each record.
You mean the faster way is to use ado database calling Execute directly?
However, I've tried but the improvement is really limitted(

 

[Dirk Goldgar]

Thanks Dirk.
I use ado recordset to open an existed table then Edit then call
AddNew then Update for each record.
You mean the faster way is to use ado database calling Execute
directly? However, I've tried but the improvement is really
limitted(

You're adding multiple records? If you can create a single append query
that selects all the records you want to append from some source table
and inserts them into the target table, it will be much faster than any
loop you can build. As I said, DAO will almost certainly be faster than
ADO, but that difference pales in comparison to the improvement to be
gained by appending all the desired records with a single query.

Please post the actual code you're using, and I'll try to suggest a more
efficient version.

 

[XR]

Well,for example:

CADORecordset rs(&db.database);
sprintf(mes,"select * from t_stock_date where f_date<=%d and f_date>=%d
and f_stockid=%u",tobeupdate[k],tobeupdate[last_date],StkID);
rs.Open(mes);
db.Release();

for(size_t j = 0 ; j < vvsdd.size(); ++j)
{
StockDailyData & sdd = vvsdd[j];
rs.Edit();
rs.AddNew();
rs.SetFieldValue("f_stockid",sdd.stkid);//calling PutFieldValue method
of recordset.
rs.SetFieldValue("f_date",sdd.date);
rs.SetFieldValue("f_high",sdd.high);
rs.SetFieldValue("f_low",sdd.low);
rs.SetFieldValue("f_open",sdd.open);
rs.SetFieldValue("f_close",sdd.close);
rs.SetFieldValue("f_volume",sdd.volume);
rs.SetFieldValue("f_amount",sdd.amount);
rs.SetFieldValue("f_tq",sdd.tq);
rs.SetFieldValue("f_dr",sdd.dr);
rs.SetFieldValue("f_drall",sdd.dr_all);
rs.Update();
Sleep(1);
}

 

[XR]

Dirk,
It's really a pity that I'm not trying to transfering some records from a
table to another

 

[Mike Painter]

Well,for example:

CADORecordset rs(&db.database);
sprintf(mes,"select * from t_stock_date where f_date<=%d and
f_date>=%d and
f_stockid=%u",tobeupdate[k],tobeupdate[last_date],StkID);
rs.Open(mes); db.Release();

for(size_t j = 0 ; j < vvsdd.size(); ++j)
{
StockDailyData & sdd = vvsdd[j];
rs.Edit();
rs.AddNew();
rs.SetFieldValue("f_stockid",sdd.stkid);//calling PutFieldValue
method of recordset.
rs.SetFieldValue("f_date",sdd.date);
rs.SetFieldValue("f_high",sdd.high);
rs.SetFieldValue("f_low",sdd.low);
rs.SetFieldValue("f_open",sdd.open);
rs.SetFieldValue("f_close",sdd.close);
rs.SetFieldValue("f_volume",sdd.volume);
rs.SetFieldValue("f_amount",sdd.amount);
rs.SetFieldValue("f_tq",sdd.tq);
rs.SetFieldValue("f_dr",sdd.dr);
rs.SetFieldValue("f_drall",sdd.dr_all);
rs.Update();
Sleep(1);
}

WHO CHANGED ACCESS AND VB WHEN I WAS NOT LOOKING!
I DEMAND TO KNOW


Here is a meningless query that represents what you want to do:
You just need to execute it using the correct method from the language you
are using.
INSERT INTO Patients ( FirstName, LastName, Address )
SELECT DOCs.FirstName, DOCs.LastName, DOCs.Address
FROM DOCs
WHERE (((DOCs.LastName)="smith"));

 

[Dirk Goldgar]

Well,for example:

CADORecordset rs(&db.database);
sprintf(mes,"select * from t_stock_date where f_date<=%d and
f_date>=%d and
f_stockid=%u",tobeupdate[k],tobeupdate[last_date],StkID);
rs.Open(mes); db.Release();

for(size_t j = 0 ; j < vvsdd.size(); ++j)
{
StockDailyData & sdd = vvsdd[j];
rs.Edit();
rs.AddNew();
rs.SetFieldValue("f_stockid",sdd.stkid);//calling PutFieldValue
method of recordset.
rs.SetFieldValue("f_date",sdd.date);
rs.SetFieldValue("f_high",sdd.high);
rs.SetFieldValue("f_low",sdd.low);
rs.SetFieldValue("f_open",sdd.open);
rs.SetFieldValue("f_close",sdd.close);
rs.SetFieldValue("f_volume",sdd.volume);
rs.SetFieldValue("f_amount",sdd.amount);
rs.SetFieldValue("f_tq",sdd.tq);
rs.SetFieldValue("f_dr",sdd.dr);
rs.SetFieldValue("f_drall",sdd.dr_all);
rs.Update();
Sleep(1);
}

You're adding multiple records? If you can create a single append
query that selects all the records you want to append from some
source table and inserts them into the target table, it will be much
faster than any loop you can build. As I said, DAO will almost
certainly be faster than ADO, but that difference pales in
comparison to the improvement to be gained by appending all the
desired records with a single query.

Please post the actual code you're using, and I'll try to suggest a
more efficient version.

Ah, I see you aren't actually using Access at all, but rather are
manipualting a Jet database using ADO in what appears to be a C program.
Unfortunately, I don't know how the ADO methods are implemented in C, so
I can't give you the detailed help that I'd hope to. I don't understand
the line

rs.Edit();

since the ADO recordset doesn't have an Edit method, to the best of my
knowledge. I see that you are looping through an array, and adding a
record for each element of the array. That will make it difficult to
create an insert query that adds all these records at once, unless the
elements of the array were themselves loaded from a select query.

One thing I notice is that your recordset appears to be based on a
SELECT statement that will return existing records from the source table
[t_stock_date]. But if you only intend to add records with this
recordset, you could just as easily open a recordset that initially
contains no records:

rs.Open("SELECT * FROM t_stock_date WHERE False")

My inclination, though, in the absence of a SELECT query that would
extract all the records to be inserted from some source, would be to use
a Command object to execute an INSERT statement for each element of the
array. I don't know, though how much faster (if any) that would be than
your recordset method. If you tried it and didn't get much improvement,
there may not be much else I can suggest.

 

[XR]

*^_^*
Thanks to both Dirk and Mike.

Well,for example:

CADORecordset rs(&db.database);
sprintf(mes,"select * from t_stock_date where f_date<=%d and f_date>=%d
and f_stockid=%u",tobeupdate[k],tobeupdate[last_date],StkID);
rs.Open(mes);
db.Release();

for(size_t j = 0 ; j < vvsdd.size(); ++j)
{
StockDailyData & sdd = vvsdd[j];
rs.Edit();
rs.AddNew();
rs.SetFieldValue("f_stockid",sdd.stkid);//calling PutFieldValue method
of recordset.
rs.SetFieldValue("f_date",sdd.date);
rs.SetFieldValue("f_high",sdd.high);
rs.SetFieldValue("f_low",sdd.low);
rs.SetFieldValue("f_open",sdd.open);
rs.SetFieldValue("f_close",sdd.close);
rs.SetFieldValue("f_volume",sdd.volume);
rs.SetFieldValue("f_amount",sdd.amount);
rs.SetFieldValue("f_tq",sdd.tq);
rs.SetFieldValue("f_dr",sdd.dr);
rs.SetFieldValue("f_drall",sdd.dr_all);
rs.Update();
Sleep(1);
}

You're adding multiple records? If you can create a single append query
that selects all the records you want to append from some source table
and inserts them into the target table, it will be much faster than any
loop you can build. As I said, DAO will almost certainly be faster than
ADO, but that difference pales in comparison to the improvement to be
gained by appending all the desired records with a single query.

Please post the actual code you're using, and I'll try to suggest a more
efficient version.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)