I have a routine that I'm trying to make faster and that is a for loop that does an insert. In this case it's a For loop with 256 iterations that will insert unsurprisingly
perform 256 individual inserts

<psuedo>
for Index = 0 to 256
 ..do insert sql statement here
</psuedo>

Now what I've done is create a table called RowGenerator that has 1000 rows in it with a single AutoInc primary key. Now I can execute one insert statement, but
turn it into and "Insert as a Select From" statement. i.e.

Insert Into Table (Col1, Col2, Col3) Select "Val1", "Val2", "Val3" From RowGenerator top 256
..this will give me my 256 row table with correct values.

Now this is lots faster than the for loop design, but my question is does DBISAM support this natively or is there a better way of doing it ?

Cheers,

Paul.

Paul

When you want the fastest way to insert data, my suggestion is to use tables
instead of queries.

In some cases you can set Exclusive access to the table to make the process
faster otherwise you can enclose the entire process within a transaction or
if the amount of records to be inserted is huge you can commit for each n
records and start a transaction again.

Eduardo

"Paul Endersby" <paul.endersby@trilogycomms.com> wrote in message
news:95792811-37ED-4387-846A-D6219B1D16D1@news.elevatesoft.com...
>I have a routine that I'm trying to make faster and that is a for loop that
>does an insert. In this case it's a For loop with 256 iterations that will
>insert unsurprisingly
> perform 256 individual inserts
>

Assuming that all inserts are the same, and the only thing that changes are
the values, I would do an explicit prepare of the query (which of course
requires that the SQL does not change, just the parameters) and sandwich the
whole thing in a transaction.

However, since you would have to have a loop in code, you could just operate
on the table and eliminate the middleman insert query. After you select the
values to insert in Q, do a

While not Q.Eof do begin
 table.insert;
 table.value := query.value;
 table.post;
 query.next;
end;

Robert

> <psuedo>
> for Index = 0 to 256
>  ..do insert sql statement here
> </psuedo>
>
> Now what I've done is create a table called RowGenerator that has 1000
> rows in it with a single AutoInc primary key. Now I can execute one insert
> statement, but
> turn it into and "Insert as a Select From" statement. i.e.
>
> Insert Into Table (Col1, Col2, Col3) Select "Val1", "Val2", "Val3" From
> RowGenerator top 256
> .this will give me my 256 row table with correct values.
>
> Now this is lots faster than the for loop design, but my question is does
> DBISAM support this natively or is there a better way of doing it ?
>
> Cheers,
>
> Paul.
>

Now that I look at my repoy again, it does not make much sense to query the
RowGenerator. Just filter it, or simply count the inserts.

{position RowGenerator as required}
database.starttransaction;
j := 0;
repeat
inc(j);
table.insert;
table.value := RowGenerator.value;
 table.post;
RowGenerator.next;
until j = 256;
database.commit;
>



"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:27B1D1E3-2461-4608-B726-14CF996FC68C@news.elevatesoft.com...
>
> "Paul Endersby" <paul.endersby@trilogycomms.com> wrote in message
> news:95792811-37ED-4387-846A-D6219B1D16D1@news.elevatesoft.com...
>>I have a routine that I'm trying to make faster and that is a for loop
>>that does an insert. In this case it's a For loop with 256 iterations that
>>will insert unsurprisingly
>> perform 256 individual inserts
>>
>
> Assuming that all inserts are the same, and the only thing that changes
> are the values, I would do an explicit prepare of the query (which of
> course requires that the SQL does not change, just the parameters) and
> sandwich the whole thing in a transaction.
>
> However, since you would have to have a loop in code, you could just
> operate on the table and eliminate the middleman insert query. After you
> select the values to insert in Q, do a
>
> While not Q.Eof do begin
>  table.insert;
>  table.value := query.value;
>  table.post;
>  query.next;
> end;
>
> Robert
>
>> <psuedo>
>> for Index = 0 to 256
>>  ..do insert sql statement here
>> </psuedo>
>>
>> Now what I've done is create a table called RowGenerator that has 1000
>> rows in it with a single AutoInc primary key. Now I can execute one
>> insert statement, but
>> turn it into and "Insert as a Select From" statement. i.e.
>>
>> Insert Into Table (Col1, Col2, Col3) Select "Val1", "Val2", "Val3" From
>> RowGenerator top 256
>> .this will give me my 256 row table with correct values.
>>
>> Now this is lots faster than the for loop design, but my question is does
>> DBISAM support this natively or is there a better way of doing it ?
>>
>> Cheers,
>>
>> Paul.
>>
>
>

Paul,

<< Now I can execute one insert statement, but turn it into and "Insert as a
Select From" statement. i.e.

Insert Into Table (Col1, Col2, Col3) Select "Val1", "Val2", "Val3" From
RowGenerator top 256
.this will give me my 256 row table with correct values.

Now this is lots faster than the for loop design, but my question is does
DBISAM support this natively or is there a better way of doing it ? >>

Yes, you can execute that with DBISAM and it will work just fine.

--
Tim Young
Elevate Software
www.elevatesoft.com