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@...
>

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@...]
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