Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-03-22
6
Medium Priority
?
989 Views
Last Modified: 2007-12-19
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 ?
0
Comment
Question by:wj81112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 300 total points
ID: 8186853
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
 

Author Comment

by:wj81112
ID: 8189059
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
 
LVL 46

Expert Comment

by:aikimark
ID: 8195044
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
 

Expert Comment

by:CleanupPing
ID: 9474505
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

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question