🚀 go-pugleaf

For Access (but not for most other DBMS) try to use TADOTable with
CursorLocation = crServer. Using transaction (open transaction, insert 5000
or 19000 rows, then commit transaction, then start new transation etc.) will
also speed up inserts (again, for other DBMS this may or may be not true).
For best results you may look at third-party components, but they may
require more code.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Dan" <Dan@do.not.spam> ???????/???????? ? ???????? ?????????: 
news:46645ac3@newsgroups.borland.com...
> Hi Folks,
>
> I need some help. I have some tables that I want to move to Access.  But 
> moving the data from one format to the other seems to take FOREVER with my 
> code.
>
> For simplicity, the source is basically two tables, a source table which 
> contains header information and forms the basis of a link to the 12 
> records in a subtable.  The sub table is basically a memo field containing 
> table-link information that, each of which has 3 fields, containing 520 
> records.  The new target format I am moving to basically breaks out that 
> memo into 520 unique records in each of three tables.
>
> As you could see, to import one complete record from the source, I would 
> have to add the following number of records to the target:
>
> 1 Header Record
> And then 12x representing each expanded subtable:
>   520 (1st field) records
>   520 (2nd field) records
>   520 (3rd field) records
>
> This is ends up being 6240+6240+6240+1 = 18,721 records!
>
> Oh, why the new format?? why???  Seriously though, there are reasons for 
> it!  For starters, the 1st and 2nd fields are the same in each 12 
> sub-tables, so I can get away with only adding them once.  Then, there are 
> a number of mathematical operations I would like to easily perform on the 
> data, that can be performed via SQL, that it is another bonus; there are 
> more.
>
> So, performing 19K Insert statements, like I said, TAKES FOREVER.
> What is the best way to do this besides the following pseudo:
>   TADOQuery *Query;
>   Query->SQL->Text = "INSERT ONE RECORD SQL STATEMENT"
>   Quey->ExecSQL();
>
> and then looping thru 19k more times?
>
>
> Thanks in advance!
>
> Dan 

Vassiliev V. V. wrote:
> For Access (but not for most other DBMS) try to use TADOTable with
> CursorLocation = crServer. 

I did this and then did a quick test off inserting 100 sets of 520 
records, and the time was exactly the same.

Using transaction (open transaction, insert 5000
> or 19000 rows, then commit transaction, then start new transation etc.) will
> also speed up inserts (again, for other DBMS this may or may be not true).
> For best results you may look at third-party components, but they may
> require more code.

How can you batch transactions in this manner?

It is difficult to say without seeing your code and data why TADOTable is 
slow. If you have no BLOBs and not much columns, inserting 19000 rows should 
take ~1-2 minutes. Transactions can be batched.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)



"Dan" <Dan@do.not.spam> ???????/???????? ? ???????? ?????????: 
news:466580d1@newsgroups.borland.com...
> Vassiliev V. V. wrote:
>> For Access (but not for most other DBMS) try to use TADOTable with
>> CursorLocation = crServer.
>
> I did this and then did a quick test off inserting 100 sets of 520 
> records, and the time was exactly the same.
>
> Using transaction (open transaction, insert 5000
>> or 19000 rows, then commit transaction, then start new transation etc.) 
>> will
>> also speed up inserts (again, for other DBMS this may or may be not 
>> true).
>> For best results you may look at third-party components, but they may
>> require more code.
>
> How can you batch transactions in this manner? 

Vassiliev V. V. wrote:
> It is difficult to say without seeing your code and data why TADOTable is 
> slow. If you have no BLOBs and not much columns, inserting 19000 rows should 
> take ~1-2 minutes. Transactions can be batched.
Interesting.. 19000 rows in 2 minutes?  Obviously one can cut and paste 
more than 19000 records in excel (I'm only doing 4 columns) in MUCH less 
than 2 minutes.  I think I can have the same type of success copying 
from Excel and pasting in Access.

How can I realize 'copy and paste' like performance inserting records in 
an Access database?

Does anyone know how to do a BatchInput with an Access database?

Copy and paste is in-memory operation. Fill database requires write to disk 
and update indexes and foreign keys that is much slower. 2 minutes is for 
case when you have enough columns in table and indexes.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Dan" <Dan@do.not.spam> ???????/???????? ? ???????? ?????????: 
news:4666194d@newsgroups.borland.com...
> Does anyone know how to do a BatchInput with an Access database? 

I'm still not at copy and paste speeds but was able to decrease insert 
times by over 80%.

I started with this:

TADOQuery *SQLObject = (pointer of actual object);
do
{
   SQLObject->SQL->Text = "Insert into [tablename] (fields...) VALUES 
(values..)";
}
while (still have to import huge number of records);
SQLObject->ExecSQL();


To increase I changed the above to something like this:
TADOQuery *SQLObject = (pointer of actual object);
SQLObject->SQL->Text = "Select (field) from [tablename] where (field 
doesn't already exist); //fast query to turn on SQL
do
{
   SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));
}
while (still have to import huge number of records);
SQLObject->BatchUpdate(arAll);


Any other suggestions on how to increase speed?  I am presently at 
around 200ms for 520 records, and I'd like to be well less than 100ms

Dan

Using TADOCommand with parameters and Prepared = true should make inserts 
faster. BatchUpdate() uses similar technique but doing this manually you may 
do it faster.

How fast can you insert data depends not only on methods but on data (how 
much columns, do you have BLOBs or not, how much indexes and which columns 
are indexed, foreign keys etc), for example one example in OLEDB Direct 
(http://www.oledbdirect.com) inserts 200'000 rows in 16.5 seconds, first 
100'000 are inserted into table (using interfaces that TADOTable uses on low 
level), second 100'000 rows are inserted inside transaction and second 
100'000 rows are inserted in 4.5 seconds. However it is simple table without 
indexes.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Dan" <Dan@do.not.spam> ???????/???????? ? ???????? ?????????: 
news:4669c1e0$1@newsgroups.borland.com...
> I'm still not at copy and paste speeds but was able to decrease insert 
> times by over 80%.
>
> I started with this:
>
> TADOQuery *SQLObject = (pointer of actual object);
> do
> {
>   SQLObject->SQL->Text = "Insert into [tablename] (fields...) VALUES 
> (values..)";
> }
> while (still have to import huge number of records);
> SQLObject->ExecSQL();
>
>
> To increase I changed the above to something like this:
> TADOQuery *SQLObject = (pointer of actual object);
> SQLObject->SQL->Text = "Select (field) from [tablename] where (field 
> doesn't already exist); //fast query to turn on SQL
> do
> {
>   SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));
> }
> while (still have to import huge number of records);
> SQLObject->BatchUpdate(arAll);
>
>
> Any other suggestions on how to increase speed?  I am presently at around 
> 200ms for 520 records, and I'd like to be well less than 100ms
>
> Dan 

On Jun 8, 4:53 pm, Dan <D...@do.not.spam> wrote:
> I'm still not at copy and paste speeds but was able to decrease insert
> times by over 80%.
>
> I started with this:
>
> TADOQuery *SQLObject = (pointer of actual object);
> do
> {
>    SQLObject->SQL->Text = "Insert into [tablename] (fields...) VALUES
> (values..)";}
>
> while (still have to import huge number of records);
> SQLObject->ExecSQL();
>
> To increase I changed the above to something like this:
> TADOQuery *SQLObject = (pointer of actual object);
> SQLObject->SQL->Text = "Select (field) from [tablename] where (field
> doesn't already exist); //fast query to turn on SQL
> do
> {
>    SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));}
>
> while (still have to import huge number of records);
> SQLObject->BatchUpdate(arAll);
>
> Any other suggestions on how to increase speed?  I am presently at
> around 200ms for 520 records, and I'd like to be well less than 100ms
>
> Dan

Hey Dan,

What's this line:

SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));


I don't see InsertRecords as a function in TADO components. I see an
InsertRecord function, note that's singular event.

-Dean

On Jun 22, 2:43 pm, DeanB <deanbrow...@yahoo.com> wrote:
> On Jun 8, 4:53 pm, Dan <D...@do.not.spam> wrote:
>
>
>
>
>
> > I'm still not at copy and paste speeds but was able to decrease insert
> > times by over 80%.
>
> > I started with this:
>
> > TADOQuery *SQLObject = (pointer of actual object);
> > do
> > {
> >    SQLObject->SQL->Text = "Insert into [tablename] (fields...) VALUES
> > (values..)";}
>
> > while (still have to import huge number of records);
> > SQLObject->ExecSQL();
>
> > To increase I changed the above to something like this:
> > TADOQuery *SQLObject = (pointer of actual object);
> > SQLObject->SQL->Text = "Select (field) from [tablename] where (field
> > doesn't already exist); //fast query to turn on SQL
> > do
> > {
> >    SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));}
>
> > while (still have to import huge number of records);
> > SQLObject->BatchUpdate(arAll);
>
> > Any other suggestions on how to increase speed?  I am presently at
> > around 200ms for 520 records, and I'd like to be well less than 100ms
>
> > Dan
>
> Hey Dan,
>
> What's this line:
>
> SQLObject->InsertRecords(ARRAYOFCONST((fieldvalues...)));
>
> I don't see InsertRecords as a function in TADO components. I see an
> InsertRecord function, note that's singular event.
>
> -Dean- Hide quoted text -
>
> - Show quoted text -

Note I am using CBuilder-6, maybe you are using a more recent version?