Hi all,
  I have written a program in Delphi 4 to transfer a DBase database into
an Interbase 6 database. This application uses the BDE and the database
parameters are set as follows:

SERVER NAME=IB_SERVER:/PATH/DATABASE.GDB
DRIVER FLAGS=4096
USER NAME=SYSDBA
OPEN MODE=READ/WRITE
SCHEMA CACHE SIZE=8
LANGDRIVER=
SQLQRYMODE=SERVER
SQLPASSTHRU MODE=SHARED NOAUTOCOMMIT
SCHEMA CACHE TIME=-1
MAX ROWS=-1
BATCH COUNT=200
ENABLE SCHEMA CACHE=FALSE
SCHEMA CACHE DIR=
ENABLE BCD=FALSE
BLOBS TO CACHE=1
BLOB SIZE=64
PASSWORD=

I am running both the application and Interbase on the same box (I have
to) a PIII 800 with 256MB ram and am currently not impressed with the
speed. I commit every 1000 records and even on a table with relatively
few fields and no blobs I can only get around 400 records a second. This
is about 10x slower than copying from and to dbase tables. I am using
paramerized queries and preparing them.

So the question is, how should I set up the database
properties/Interbase to maximise the import of this data?

Regards
  Ian Newby

Hi Philip,
  Thanks for your reply. Some points for clarification.

There are no TTables or select Queries performed on the Interbase
database. There are only parameterized insert queries performed to add
the data. Therefore no cached updates are required.

Like you the program first copies the table structure by producing a
create table SQL statement (after first making sure the table and field
names are not interbase keywords).

The current purpose of this tool is to run obtain an Interbase reporting
database from a current dbase application. Therefore it will run via
NT's AT command (or cron) at midnight so I need it to be as simple as
possible to fire. The current approach does this. I don't want to do a
two stage, dbase -> fixed length -> Interbase if I can aviod it.

Regards
  Ian Newby

Ian,

Some ideas:

If you're using cached updates, then you have to commit more often than
every 1000 records and you have to close and open the target query each
time you commit.

Take a look at TBatchmove. It automates much of this kind of process.

Also, I've had good results by just copying the data, table by table into
IB from the source database, so that the IB database starts as a mirror
image of the source database. I use special names for these IB tables to
indicate that they are imported data. Then I use SQL, stored procedures and
setup programs to move this data into the proper places in the permanent IB
tables.

Finally, if dBase can give you ascii files where the fields are fixed
length, you can use IB's external table specification to read these
directly into IB quickly. See the documentation for the CREATE TABLE
statement.

HTH

Phil Cain

--

Ian,

In that case I'd try TBatchMove. I haven't actually used it myself because
all my migrations tend to be one-time things. But I've heard a lot of good
things about TBatchMove and there are numbers of posting on it in the
sqlservers newsgroup.

Good luck.

Phil Cain
--