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 ?
wj81112Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Rather than passing the values in by parameter, try doing this with dynamic SQL.
Example
ADOQuery1->Prepared=false;
for(int i=0;i<j;i++)
  {
   ADOQuery1->SQL->Add("Insert INTO test2 (mykey, mydata) Values (1000, 'ADOQuery')");
   ADOQuery1->ExecSQL();
   ADOQuery1->SQL->Clear();
  }
_________________________________________
Alternatively, create an Access2000 querydef object and use the ADO Command object to assign it's parameter values.  This is faster, because the SQL is known to the database engine and needs no preparation.
_________________________________________
Also, look at the indexes and relationships in/with the test2 table.  They can limit the performance of your INSERT operations.
_________________________________________
As a last resort, you might consider inserting blocks of rows using transactions.  This reduces the number of Commits during the loading of your data.
0
 
wj81112Author Commented:
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!
0
 
aikimarkCommented:
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.
0
 
CleanupPingCommented:
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.
0
All Courses

From novice to tech pro — start learning today.