Subject RE: [firebird-support] Re: At my wits end
Author Epstein, Ed
I have thought about much the same method. I can write stored procedures
for the database. Some exist for certain jobs already. However, I dont
have any UDFs for string processing or anything like that. I can only
imagine how complex it is. I was also concerned that the processing power
required to do it would be greater than the Dual PIII 1 GHZ could provide.

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: bjonessb [mailto:bjonessb@...]
Sent: Friday, March 12, 2004 8:05 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: At my wits end


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




Yahoo! Groups Links