I'll throw in my two cents worth. One of the applications I write
involves daily imports. I use IBpump by Clever Components. I use
the DLL and compile it into my application. I don't know by, but
IBpump is much faster than any other tools I've tried. I get about
1200 inserts per second, and I'm inserting about 40 fields, and
about 800 bytes per record. This is over a 100MB connection.

I have played with another solution that is even faster, but have
not implemented it yet. Are you able to create store procedures on
your server? If so, you could insert the entire file into a blob
field in a "staging" table on the server. This is not much slower
than just copying a file from a client computer to the server. It
would probably take a few seconds in your case. Then you could
write a stored procedure to parse this blob field, and insert the
records into the destination table(s). In my test, I net about 5000
inserts per second, but the stored procedure has gotten a little
complicated.

Bill

--- In firebird-support@yahoogroups.com, "Epstein, Ed"
<eepstein@c...> wrote:

> First of all I would like to apologize for any improper posting.

The

> production server here at my company is IB V7.0.0.1. All my

personal

> projects started out as IB V6, but since its not under constant

development,

> firebird 1.5. I did not mean to upset anybody.
>
> As for my use of tps, I meant transactions per second. I guess I

should

> really be saying Inserts per second. I personally have tried

commits from

> around 10-10000. The C++ test code was committing every 1000

records.

>
> The test code was an c++ external app. However, if it worked

correctly the

> coders would of changed it into a component I could use inside of

VB6

> therefore not making it an external application anymore. The

component

> would of only needed to support multiple SQL statements at a time

and return

> SQL error codes if any.
>
> An external table cannot be done. This is since the server itself

resides

> on a dedicated server within the network. The ability to have the

frontend

> create an external file and transfer it to a place accessible by

the server

> does not exist. The program must be able to work from outside the
> corporation as well. Also, I have been told that the server

itself might

> change platforms from Win2k to Linux. So creating a client/server

program

> to transfer the external table is also not a viable idea.

Basically, the

> only way to get data to the server is through a connection to the

server

> only.
>
> As for data itself...
>
> Almost every day of the week files will be coming in that need to

processed.

> They are all CSV formatted. They can contain anywhere from 100-

1000000

> records. I would have to say that about 750,000 records per week

is the

> average. The size of each record is averaged about 200 bytes (see

the end

> of the post). It is being moved into a single table with 2 or 3

indexes on

> it. ( All of the tests being performed were with 10 varchar(30)

fields with

> no indexes, triggers, generators, or constraints)
>
> I completely understand that ADO and ODBC (and OLE) do not fall

into the

> "lean" layer category. I did not mean to tar firebird with

interbase 7's

> brush either. I know that the performance lies in my setup

somewhere and is

> not an inherent factor of either interbase or firebird.
>
> Finally, the reason that the files cannot be scheduled to be

imported into

> the database using other means is time. The results of each of

these files

> must be ready within the same day, if not hours. Waiting 24-48

hours for the

> data to be imported is not acceptable in this case. The frontend

must be

> able to accomplish this on its own. Its a specific requirement.
>
>
> I don't mean to be difficult to get information out :) I

appreciate all of

> your help. Thanks.
>
>
> There are 14 varchar fields. Allowing for the 2 byte overhead the

varchar

> data can range from 44 bytes to 250 bytes. The other fields are

30 bytes

> altogether. So each record ranges from 77 bytes to 280 bytes
>
>
> CREATE TABLE PULLMASTER
> (
> HEXID VARCHAR( 6)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> PHONE DOUBLE PRECISION NOT

NULL,

> FIRSTNAME VARCHAR( 30)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> MI VARCHAR( 1)

CHARACTER SET

> ASCII COLLATE ASCII,
> LASTNAME VARCHAR( 30)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> ADDRESS VARCHAR( 50)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> CITY VARCHAR( 30)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> ST VARCHAR( 2)

CHARACTER SET

> ASCII NOT NULL COLLATE ASCII,
> ZIP VARCHAR( 5) NOT

NULL,

> ZIP4 VARCHAR( 4),
> SAL VARCHAR( 5)

CHARACTER SET

> ASCII COLLATE ASCII,
> CUID VARCHAR( 25)

CHARACTER SET

> ASCII COLLATE ASCII,
> CDATE TIMESTAMP

DEFAULT

> CURRENT_TIMESTAMP,
> DISPO SMALLINT,
> STATUS SMALLINT,
> TIMEZONE SMALLINT,
> FIRSTNAME2 VARCHAR( 30)

CHARACTER SET

> NONE COLLATE NONE,
> MIDDLENAME2 VARCHAR( 1)

CHARACTER SET

> NONE COLLATE NONE,
> LASTNAME2 VARCHAR( 30)

CHARACTER SET

> NONE COLLATE NONE,
> BID INTEGER,
> SID INTEGER,
>
> );
>
>
>
>
> Edwin Epstein
> > Continuity Partners Inc.
> > 2753 S. Highland Dr.
> > Suite 1010
> > Las Vegas, Nevada 89109
> > (702) 851-7850 Ext. 247
> > eepstein@c...
> >
> CONFIDENTIALITY NOTICE: The information contained in this message

may be

> privileged and confidential and protected from disclosure.
> If the reader of this message is not the intended recipient, or an

employee

> or agent responsible for delivering this message
> to the intended recipient, you are hereby notified that any

dissemination,

> distribution or copying of this communication is
> strictly prohibited. If you have received this communication in

error,

> please notify us immediately by replying to the message and
> deleting it from your computer.
>
>
>
>
> -----Original Message-----
> From: Helen Borrie [mailto:helebor@t...]
> Sent: Friday, March 12, 2004 2:23 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] RE: At my wits end
>
>
> At 01:02 PM 12/03/2004 -0800, you wrote:
> >I have been coding with VB6 and naturally have had many issues

with working

> >with Interbase/Firebird etc. After much testing I decided to use
> >SIBProvider. Its very adequate for my needs. However, I need to

move

> >500K-1M records at a time into the database and that has proven

an near

> >insurmountable challenge ( I only say near since I still have a

little bit

> >of hope). After coming to the obvious conclusion that the ADO

and OLEDB

> >layers are slowing the transactions down, the obvious answer was

to bypass

> >them entirely. Only 3 solutions presented themselves (largely in

part to

> >this forum):
> >
> >1) Use the API directly - Don't have the skill
> >2) Use an external program written to use the API - SQL scripts,

import

> >engines like FBExport, etc. I really needed a way to do it

internally and

> >they did not even reach an accetable speed.
> >3) Use external tables. This does not work since I would have to

come up

> >with a method of delivering them to the server. This has proved

to be

> quite
> >impossible due to circumstances that I cannot control.
> >
> >I took the advice of this forum and paid to have a component

written to

> >execute the SQL statements for me and use only the API to do

this. No ADO

> >layers, etc. Just pure looped API statements.
> >
> >This does EVEN worse than ADO and OLEDB. I received multiple

testing

> >programs from potential coders and none of them could even break

100 tps,

> >let alone the 1500 tps i need.
>
> Questions
>
> tps = what? Are you trying to do this one transaction per record?
> If not (and hope not) how many records are being committed per

transaction?

>
> What kind of source input data is it? Record size?
>
> Are you importing into one table or several?
>
> Are the destination tables indexed?
>
> What's the reason you can't use an external tool for these

imports? (It

> seems the obvious thing if your only options are ADO or ODBC).

It's

> impossible to tell what you're getting for your dollar from the

API coders

> but won't a custom C application be "external" as well? Wouldn't

your

> dollar be better spent on an existing tool, or on coding an

application to

> convert the data into a format suitable for an external table?
>
>
> >So I am at my wits end here. Its got to be the server.
>
> >Interbase V7.0.0.1
> >
> >No other services running on the server. Purely dedicated to the

database

>
> Have you considered applying the patches to IB 7? It's now at

7.1.2,

> having been through a major 7.1 "upgrade" and received two

subsequent bug

> patches.
>
> It don't "got to be the server", although without the bugfixes

it's

> probably playing its part. For this volume of import you'd better

have the

> leanest possible connectivity layer. ODBC and ADO providers don't

fall

> into the "lean" category.
>
> >So I just really
> >need to know if anybody out there is getting more than 1500 tps

to their

> >Interbase/Firebird server and what their setup is. I love

Interbase to

> >death, really I do. I cannot even change it if I wanted to.
>
> Have you tried (as a test) using Firebird 1.5 to import the data

to an

> independent database and then pumping it? Don't automatically tar

Firebird

> with InterBase 7's brush...
>
> /heLen
>
>
>
>
> Yahoo! Groups Links