asked on

How to Speed up insert records into Access2000 by ADO in Borland C ++ builder 6.0 ?

Hi ! 3x for care !I find it difficult to speed up inserting records into
Access2000 by ADO in Borland C ++ builder 6.0 . now the speed is 15000 40 secs .
And my key code is :
ADOQuery1->Close();
ADOQuery1->DisableControls();
ADOQuery1->ExecuteOptions = ADOQuery1->ExecuteOptions << eoExecuteNoRecords;
ADOQuery1->Prepared=true;
ADOQuery1->CacheSize = StrToInt(EdtCacheSize->Text);
ADOQuery1->LockType = ltBatchOptimistic ;
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Add("Insert INTO test2 (mykey, mydata) Values (:Key,:Data)");
for(int i=0;i<j;i++)
{
ADOQuery1->Parameters->ParamByName("Key")->Value = "1000";
ADOQuery1->Parameters->ParamByName("Data")->Value = "ADOQuery";

ADOQuery1->ExecSQL();
}
I think the "UpdateBatch" method of ADOQuery will make it faster,
but I can make it out
could u help me ?

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wj81112

ASKER

to:aikimark
thanks for your attention.my table has no relationship now for I have only one table in my database in order to test the speed.
I have tried these codes :
ADOQuery1->SQL->Add("Insert INTO test2 (mykey, mydata) Values (1000, 'ADOQuery')");
ADOQuery1->ExecSQL();
ADOQuery1->SQL->Clear();
however, it's pityful that the speed is slower :(

Fortunately, the method of Transction does work well !
Now my codes :
str3=" Insert Into test2 (mykey, mydata) Values (:Key,:Data)";
ADOConnection1->Open();
ADOCommand1->CommandText = str3;
ADOConnection1->BeginTrans();
ADOCommand1->Prepared = true;
ADOCommand1->ExecuteOptions = ADOCommand1->ExecuteOptions << eoExecuteNoRecords;
for(long i=0;i<j;i++)
{
ADOCommand1->Parameters->ParamByName("Key")->Value = "2000";
ADOCommand1->Parameters->ParamByName("Data")->Value = "ADOCommand";
ADOCommand1->Execute();
}
ADOConnection1->CommitTrans();
ADOConnection1->Close();
the speed is 11 secs per 5000 records with transaction
and 16 secs per 5000 records without transaction.
Thank you very much!!
You also said that :
I can " create an Access2000 querydef object and use the ADO Command object to assign it's parameter values. "
However after I create such a object in Access ,
I can't find a way to use it with dynamic attributes , even in Access. It seems to me that this object is merely designed for use in Access .
Would you have any idea about how to use it ?

Now a small question : is there any tip for the use of
transaction ?
Hoping for answers online!

aikimark

I'm not a C++ person, so I'm not sure of the syntax, but try:
ADOCommand1->StoredProc = name_of_query;

Then use the existing parameter handling code to assign values to your query's parameters.

CleanupPing

wj81112:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.