Subject | Re: [IBO] Big Batch... |
---|---|
Author | Svein Erling Tysvær |
Post date | 2001-03-19T10:23:28Z |
David,
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); //Don't remember whether BeginBusy this a
property of TIB_DSQL, use ib_transaction1 if not.
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;
finally
IB_Transaction1.Commit;
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.
Set
At 16:34 16.03.2001 -0900, you wrote:
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); //Don't remember whether BeginBusy this a
property of TIB_DSQL, use ib_transaction1 if not.
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;
finally
IB_Transaction1.Commit;
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.
Set
At 16:34 16.03.2001 -0900, you wrote:
>
>
>Geoff Worboys wrote:
>
>> I suppose the question is; How fast is it going?
>
>4+ minutes, 14K+ inserts.
>
>> From my experience you should probably be getting well over 1000
>> records per second for the simple table described.
>
>I don't think so..
>
>> On good hardware
>
>Workstation : 533mhz Celeron 128MB Ram
>Server : Dual-Proc PPRO 200MHZ - unfortunately, disk is IDE, no RAID or
>anything. But with one user, shouldn't be a significant performance
>problem.
>
>> It should hopefully be obvious that speed will tend
>> to drop off quickly if your tables include blobs.
>
> No blobs in this one.
>
>> If you are not getting speed around this level then there is something
>> wrong. Probably your loop is not tight enough,
>
>See anything here that could be tightened up?
>
> for i := 1 to tsl2.Count do
> begin
> dsql.SQL.Text := 'INSERT INTO SYMBOLS (SYMBOL) VALUES ('''
> + tsl2.Strings[i-1] + ''')';
> dsql.ExecSQL ;
> end;
>
>> or you are calling
>> ProcessMessages to often.
>
>Not calling it at all. How would that apply here?
>
>> It is likely that you may have seen faster figures on a local Paradox
>> database, since it does not have to worry about such insignificant
>> issues as data integrity :-)
>
>BTW, I've tried both indexed and unindexed, primary key and no primary
>key approaches, no difference is evident. Currently table is unindexed,
>no primary key.
>
>I empty the table each time, then run the above listed routine.
>
>> You could write a component that appeared to do that - but it would
>> still be a dsql sitting in a tight loop sending record after record to
>> the server.
>
>Just a thought.
>
>Again, when I tried to use multiple threads to speed this up, I get
>connection read or write errors. Since this is a 100mbit lan connection,
>using 6 ft CAT5 RJ45 cables connected to the same switch, I don't think
>this is truly connection related. All NICS are 100BT. I use the IP
>Address of the Database Server for the ServerName property on the
>IB_Connection component, so there should be no DNS queries.
>
>Below is the .DFM code which illustrates some of the settings:
>
> object Table1: TIBOTable
> Active = True
> DatabaseName = '*.*.*.*:f:\ib_db\tradedata\ibqd.gdb'
> IB_Connection = database1
> TableName = 'SYMBOLS'
> Left = 396
> Top = 418
> end
> object database1: TIB_Connection
> DefaultTransaction = IB_Transaction1
> Params.Strings = (
> 'SERVER=*.*.*.*'
> 'PATH=f:\ib_db\tradedata\ibqd.gdb'
> 'PASSWORD=*********'
> 'USER NAME=********'
> 'PROTOCOL=TCP/IP')
> Left = 286
> Top = 400
> end
> object IB_Transaction1: TIB_Transaction
> IB_Connection = database1
> AutoCommit = True
> Isolation = tiConcurrency
> Left = 504
> Top = 408
> end
> object DSQL: TIB_DSQL
> DatabaseName = '*.*.*.*:f:\ib_db\tradedata\ibqd.gdb'
> IB_Connection = database1
> Left = 226
> Top = 136
> end
>
>Will keep plugging away...
>
>Thanks.
>
>DK
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>