Subject Re: [IBO] Volume Data Adding
Author Geoff Worboys
> That sound like it might help me out as well.
>
> Is there any chance you could pull that out and send it to me
directly
> as yahoogroups is behind the firewall here so I can't see it.

Its a big file - over 3Mb - and I dont want to start sending it around
by email.

Whoops!!! Just checked the date on that archive file, and it seems it
does not go through to March anyway. Below are some of the snippets I
was talking about. I've not acknowledged the authors, I dont think
they will mind. I just cut-n-paste in chronological order (skipping
large chunks).


HTH

Geoff Worboys
Telesis Computing



var
tmpCol: TIB_Column;
i: integer;
begin
dsql.SQL.Text := 'INSERT INTO SYMBOLS (SYMBOL)' +
' VALUES (:SYMBOL)';
dsql.Prepare;
tmpCol := dsql.ParamByName( 'SYMBOL' );
for i := 0 to tsl1.Count - 1 do
begin
tmpCol.AsString := tsl2.Strings[i];
dsql.ExecSQL;
// make the app responsive during long processing
if (i mod 1000) = 0 then
Application.ProcessMessages;
end;
end;

Remember to use tmpCol.Clear if you want to insert a NULL value as IBO
will not clear parameters between calls to ExecSQL.



The basic steps are:
- Use a TIB_DSQL and write the parameterized SQL once.
- Prepare it.
- Get a reference to your parameters in temp vars. Faster than using
ParamByName. This is only important in a loop like the yours, not in a
single param assignment.
- Call BeginBusy or another function that will stop the cursor's image
from
being changed according to the component's state (so less CPU cycles
are
wasted in the GUI).
- Start a transaction.
- Do your loop by reading your input values, assigning them to
parameters
and calling Execute.
- Commit the transaction.
- Call EndBusy.
- Unprepare the TIB_DSQL if you aren't going to use it again.
- Protect the relevant code with try-finally (for EndBusy and Commit)
or
include also try-except (just in case you want to Rollback if your
insertion
loop raises an exception instead of forcing a Commit in the finally
part).



you've gotten pretty good advice so far, but noone seems to have
noticed
your components. You have to do two changes to maximize speed:

object IB_Transaction1: TIB_Transaction
IB_Connection = database1
AutoCommit = False //Don't use true for batch inserts
Isolation = tiConcurrency
Left = 504
Top = 408
end
object DSQL: TIB_DSQL
DatabaseName = '*.*.*.*:f:\ib_db\tradedata\ibqd.gdb'
IB_Connection = database1
IB_Transaction = IB_Transaction1 //You must include the
transaction in
your DSQL
Left = 226
Top = 136
end

If you then change your code to something like
var
i: integer;
begin
try
dsql.BeginBusy(True);
dsql.SQL.Text := 'INSERT INTO SYMBOLS (SYMBOL)' +
' VALUES (:SYMBOL)';
dsql.Prepare;
for i := 0 to tsl1.Count - 1 do
begin
dsql.Params[0].AsString:=tsl2.Strings[i];
dsql.ExecSQL;
if (i mod 1000) = 0 then
Application.ProcessMessages;
end;
IB_Transaction1.Commit;
finally
dsql.EndBusy;
end;
end;
(yes, I stole this code from Geoff, just simplified it to suit the
lazy
amongst us).

I'd expect you to multiply your performance