Peter,

- As said, you definitely want to prepare statements
- Study your data access technology to OLE/DB and make sure you use settings
oriented towards fast reading (and reading only). This can make a huge
difference on big tables. If you use ADO, I personnaly use the following
settings :

CursorLocation := clUseServer;
CursorType := ctOpenForwardOnly;
LockType := ltReadOnly;

but you might find betters ones.

- Also be very careful when assigning source fields to destination params.
Avoid calls like FieldByName or ParamByName absolutely, or at least do your
mapping before the loop and use exlusively field and param *objects* in the
loop.
- Study the fields and params objects of you data access components to find
the most efficient way to do the assignement. Using Delphi and TADOQuery at
the source and TIBSQL at the destination, I had good results with the
following :

for i := 0 to FSqlBatch.Params.count - 1 do
FSqlBatch.Params[i].AsVariant := FSelectDataset.Fields[i].AsVariant;


... but again you might find something better depending on your case.

For example, for TIBSQL to TIBSQL assignments, I use

for i := 0 to FSqlBatch.Params.count - 1 do
FSqlBatch.Params[i].Assign(FSelectDataset.Fields[i]);

- At end, I can fetch millions of records in minutes.

- However, 8 hours for 2 million records seems really bad to me even if you
don't use the above tricks. Check your code or do some profiling to see if
you don't have something unrelated to the pumping process in your way.

Hope it helps.

--
Jerome

----- Original Message -----
From: gorepj
To: ib-support@yahoogroups.com
Sent: Thursday, April 03, 2003 7:18 PM
Subject: [ib-support] FAST DATA PUMPING - BEST PRACTICE


I have an application that uses OLE/DB to pump data from an AS/400
database to a Firebird 1.5 Database. Records are read and
reformatted and posted to the Firbird database. It works reliably
but too slow. It took 8 hours to post 2 Million records into a
table. I need to cut this down to say 1 or two hours.

The application reads each record from the AS/400 and constructs an
SQL statement to insert the record into the Firebird database table
i.e.

INSERT INTO FBTable (MyField1, Myfield2 ...) VALUES (MyValue1,
MyValue2...)

Is there a faster way? Would parameter substitution help?
Regards
Peter Gore


Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.