Aage -

When I posted this issue, I had no idea how far this was going to go.
Shows we've got an excellent community out there...

Aage Johansen wrote:

>
> No help, sorry, just questions:
> 1. If the IB server runs on another machine, how busy is it when you run
> the program (CPU/disk-activity)?

Virtual standstill. The host has 192 megs ram, 118 of which is in use
nominally. This host hosts a web & mail server, IB, a D5 cgi server, and
2 D5 App Servers. Currently this is a prototypical lab machine, so there
is no multi-user activity, except that which I generate
programmatically.

Incidentally, Jason, Geoff, the threads I was talking about in an
earlier post are D5 threads, not IB threads. In each thread I would
create a uniquely named IB Connection & DSQL component, so each thread
would use the IB Conn's default session. Yet I was still getting errors
reading or writing data from the connection.

> 2. If you comment out the database stuff, how is the rest of the code
> performing (any bottlenecks outside of IB/IBO)?

Take a look for yourself. This code, after significant testing and
debugging, works reliably. The only slow part is the database code. This
now executes in approx. 39-42 secs.

First we start with a pushbutton.click routine that initializes the
threads:

procedure TForm1.Button1Click(Sender: TObject);
var
i : smallint;
begin
for i := 1 to 4 do
begin
fname := i;
sym3Thread.Create(false);
sleep(100);
end;
end;

fname is a global that each thread reads initially.

Next comes the thread code itself:

type
sym3Thread = class(TThread)
private
{ Private declarations }
protected
procedure Execute; override;
end;

procedure buildSymbolList(count : smallint);

implementation

uses main, symthread;

procedure buildSymbolList(count : smallint);
var
i,n,quote1,quote2,
quote3,quote4 : smallint;
s,scom : string;
tsl : TStringList;
begin
n := count; //this holds the loop counter reference...

if n = 1 then
form1.Panel5.Caption := 'Loading Symbols...';
tsl := TStringList.Create;

//the following are the downloaded symbol files...

case n of
1 : tsl.LoadFromFile('*\nasd.csv'); //actual path replaced
with *
2 : tsl.LoadFromFile('*\nyse.csv');
3 : tsl.LoadFromFile('*\amex.csv');
4 : tsl.LoadFromFile('*\otcbb.csv');
end;

// the symbol files need to be cleaned up a little...

tsl.Delete(0);
tsl.Delete(0);
tsl.Delete(tsl.count-1);

// the symbols must be parsed out of the files...

for i := 0 to tsl.Count - 1 do
begin
s := tsl.Strings[i];
quote1 := pos('"',s);
delete(s,quote1,1);
quote2 := pos('"',s);
delete(s,quote2,1);
quote3 := pos('"',s);
delete(s,1,quote3);
quote4 := pos('"',s) - 1;
if n = 4 then
scom := copy(s,1,quote4) + '.OB'
else
scom := copy(s,1,quote4);
tsl.Strings[i] := scom;
if tsl.Strings[i] = '' then
tsl.Delete(i);
end;

// here we copy the stringlist from
// a local stringlist to a global string..

case n of
1 : stSym1 := tsl.Text;
2 : stSym2 := tsl.Text;
3 : stSym3 := tsl.Text;
4 : stSym4 := tsl.Text;
end;
s := '';
tsl.Free;

// if this is the last thread, we initiate
// the final routine which runs in the main
// VCL thread..

if n = 4 then
countSymbols;
end;

procedure sym3Thread.Execute;
begin
{ Place thread code here }
buildSymbolList(fname);
end;

/* Here's where the database rubber meets the road... */

procedure countSymbols;
var
tsl2 : TStringList;
i : smallint;
tmpCol: TIB_Column;
begin
t1 := time;
with form1 do
begin
panel5.Caption := 'Inserting into database...';

// table1 is a TIBOTable that is bound to a
// dbgrid...

table1.Close;
table1.Open;
table1.Edit;

// current iteration of table schema is
// unindexed, so we empty the table prior
// to inserting the latest symbol data...

table1.EmptyTable;
tsl2 := TStringList.Create;

// here we concat the global strings that
// were populated in the threads into one big
// stringlist...

tsl2.text := stSym1;
tsl2.text := tsl2.text + stSym2;
tsl2.text := tsl2.text + stSym3;
tsl2.text := tsl2.text + stSym4;
stSym1 := '';
stSym2 := '';
stSym3 := '';
stSym4 := '';

// the following code is based on advise
// from Jason, Geoff & others - this code
// brought the time down to about 40 secs
// from 4+ minutes...

ib_transaction1.BeginBusy(true);
dsql.SQL.Text :=
'INSERT INTO SYMBOLS (SYMBOL) VALUES (:SYMBOL)';
dsql.Prepare;
tmpCol := dsql.ParamByName( 'SYMBOL' );
for i := 1 to tsl2.Count do
begin
tmpCol.asString := tsl2.Strings[i-1];
dsql.ExecSQL;
if (i mod 1000) = 0 then
Application.ProcessMessages;
end;
ib_transaction1.EndBusy;
end;
form1.panel5.Caption := 'Symbols Loaded: '
+ intToStr(tsl2.count);
tsl2.Free;
t2 := time;
decodeTime(t2 - t1,hr,min,sec,msec);
form1.label4.Caption := floatToStr(min) + ':' + floatToStr(sec);
end;

The only bottleneck that I am aware of is the data entry code. I've yet
to figure out how to use this IB Affinity utility to get IB to use only
one processor...

David Keith

[Non-text portions of this message have been removed]