<Repost> Help!! Data Import/Batch Input Questions

Daryl Zavier

unread,

Nov 8, 2001, 5:10:59 AM11/8/01

to

Guys,

I'm currently facing a problem with a client/server application
I'm writing for my company. 1 of the function of the application
would be to allow the user to import the monthly data file from
my client PC(in dbf format - foxpro) which is downloaded from
our main central server into the local country interbase server.
Currently it takes about 2-3hrs for the import process which way
too long (DBF file is about 30mbs, roughly 50000 records).

My current import screen is a simple 6 component setup
(IBDatabase, IBTable, IBTransaction, IBUpdateSQL - for the
destination table; TDatabase, TQuery - for the source table).
It's a straightforward while not eof, insert procedure which I'd
use the TQuery component as the Source DataSet to pump into the
IBTable component (all column mappings are coded in the
procedure). I'm also forcing the IBTransaction to commit the
records in 5% increments in a effort to clear my client-side
cache or to improve performance (since the local Borland Dude
told me to try that).

All said and done, it still takes me about 2hrs+ just to import
the data into my interbase server and that's not something which
I can present to my management team! So gurus out there, please
drop me a note or something in here or my email if it ain't too
much trouble. Anyone, everyone....SOS!!! ^_^

Cheers!
Ps: I'm using Delphi 6 Professional, heard that the Enterprise
version would have a import wizard to solve my problem, sigh.

Ruaan Barnard

unread,

Nov 8, 2001, 6:29:12 AM11/8/01

to

Hi

Do not use a TIBTable component. Rather use a TIBDataset or TIBQuery to get
the records into the db.
What do you use the TIBTable for. You can read the Query and parse each
record into the required
parameters and then use the TIBUpdateSQL to insert the data into the gdb.

One of our apps is an Enterprise system which handles multiple branches.
I have used the importer yesterday and imported 60000 records in 8 minutes
over a normal LAN.

Bottom line - do not use TIBTABLE for anything.

Regards

Ruaan

Mark Di Val

unread,

Nov 8, 2001, 7:42:03 AM11/8/01

to

I would use a TIBSQL to do the inserts.

For your destination use TIBDatabase, TIBSQL, TIBTransaction.

In the SQL field write:

insert into desttable (FIELD1, FIELD2, FIELD3,...)
values (:FIELD1, :FIELD2, :FIELD3,...)

When you have a source record ready

try
IBTranaction1.StartTransaction;

IBSQL->ParamByName("FIELD1").Value = SourceField1.Value;
IBSQL->ParamByName("FIELD2").Value = SourceField2.Value;
IBSQL->ParamByName("FIELD3").Value = SourceField3.Value;
...

IBSQL.ExecQuery;

IBTransaction1.Commit;
except
IBTransaction.Rollback;

// Failure message, exit, or otherwise handle error.

end;

It should fly!

HTH

Mark

Wayne Niddery [TeamB]

unread,

Nov 8, 2001, 9:59:03 PM11/8/01

to

"Daryl Zavier" <dza...@hotmail.com> wrote in message
news:3bea5a33$1_1@dnews...

>
> I'm currently facing a problem with a client/server application
> I'm writing for my company. 1 of the function of the application
> would be to allow the user to import the monthly data file from
> my client PC(in dbf format - foxpro) which is downloaded from
> our main central server into the local country interbase server.
> Currently it takes about 2-3hrs for the import process which way
> too long (DBF file is about 30mbs, roughly 50000 records).

As far as doing this through an application, you have already got good
advice - in particular, use TIBSQL to perform the inserts (and committing
after every n records is good advice, I usually use 1000 for n).

However an alternative that may be fastest would be to export the foxpro
files into a fixed-field ASCII file. This ASCII file can then be declared as
an External table in Interbase. If the field mappings are compatible with
the internal Interbase tables to be inserted into then the import can be
done with a single query statement:

insert into table (fld1, fld2, fld3)
select flda, fldb, fldc from externaltable

If the mappings are more complex, it can be done in a stored procedure.

Whichever way you do this, another thing that will speed up the import is to
inactivate all indexes for that table in Interbase, perform the import, then
reactivate the indexes (which causes a rebuild of the indexes). Note that
Primary and Foreign key indexes cannot be inactivated, but all other indexes
can.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

Mark Di Val

unread,

Nov 9, 2001, 4:16:48 AM11/9/01

to

What was the syntax to declare the external table?

I can only see 'declare table' in the Interbase SQL reference
and cannot see how you indicate to IB that it is external.

Suppose you have an ascii text file "import.txt" - how do you
pick it up in your select?

TIA

Mark

Craig Stuntz (TeamB)

unread,

Nov 9, 2001, 9:49:42 AM11/9/01

to

Mark Di Val wrote:
>
> What was the syntax to declare the external table?
>
> I can only see 'declare table' in the Interbase SQL reference
> and cannot see how you indicate to IB that it is external.

See CREATE TABLE EXTERNAL FILE... in InterBase Language Reference.

HTH,

-Craig

--
Craig Stuntz (TeamB) · Vertex Systems Corp. · Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
Delphi/InterBase WebLog: http://delphi.weblogs.com