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