| Subject | Re: [firebird-support] OLEDB VERY SLOW. Need > 1000 transactions /s |
|---|---|
| Author | David Johnson |
| Post date | 2004-02-06T06:30:04Z |
I have been doing some performance testing and others on this list have assisted me.
My first observation is that your need for millions of rows in minutes translates into a need for bigger hardware. We run about 12,000,000 transactions per hour on DB2 on maxed out IBM 2500. Are you asking for a desktop system with IDE hardware to achieve throughput comparable to high end mainframe with DASD hardware?
Superserver Interbase on a high end Opterex system with fast RAID running 64 bit linux might be able to get into this game, but I would not hold out much hope for VB, and Windows.
Thread safe "in-memory" operations (no DBMS) under Windows peak at 2.5 million TPS on an 800 MHz singgle CPU box. If, instead of using specail assembly routines for thread safety you use Windows OS functions, your performance is reduced to 500,000 TPS. There was no DBMS involved in the operations generating these timing figures.
When you add the RDBMS overheads into the mix, and you use a connectivity class, you need to add the penalties for moving data into and out of variants before they get to the DBMS buffer layer. In my measurements (Delphi IBX and SQLExpress so far), these overheads were 30% of the transaction time for insert operations. To turn the equation around, I can improve performance on inserts in my application by 30% by programming to the gds API directly, instead of going through connectivity objects. Your application sounds like one that would benefit from squeezing every cycle out of the system that you can.
My first observation is that your need for millions of rows in minutes translates into a need for bigger hardware. We run about 12,000,000 transactions per hour on DB2 on maxed out IBM 2500. Are you asking for a desktop system with IDE hardware to achieve throughput comparable to high end mainframe with DASD hardware?
Superserver Interbase on a high end Opterex system with fast RAID running 64 bit linux might be able to get into this game, but I would not hold out much hope for VB, and Windows.
Thread safe "in-memory" operations (no DBMS) under Windows peak at 2.5 million TPS on an 800 MHz singgle CPU box. If, instead of using specail assembly routines for thread safety you use Windows OS functions, your performance is reduced to 500,000 TPS. There was no DBMS involved in the operations generating these timing figures.
When you add the RDBMS overheads into the mix, and you use a connectivity class, you need to add the penalties for moving data into and out of variants before they get to the DBMS buffer layer. In my measurements (Delphi IBX and SQLExpress so far), these overheads were 30% of the transaction time for insert operations. To turn the equation around, I can improve performance on inserts in my application by 30% by programming to the gds API directly, instead of going through connectivity objects. Your application sounds like one that would benefit from squeezing every cycle out of the system that you can.
----- Original Message -----
From: Alan McDonald
To: firebird-support@yahoogroups.com
Sent: Thursday, February 05, 2004 7:56 PM
Subject: RE: [firebird-support] OLEDB VERY SLOW. Need > 1000 transactions /s
> I am using VB6, AD0 V2.5 and SIBProvider (Have also tried IBPHOENIX
> free & trial). Using ADO Connection & command objects I have tried
> to perform insert statements on tables in my backend. I have tried
> it with a simple insert statement, a statment executing a stored
> procedure, and a command object executing a store procedure with
> named parameters. The last is by far the fastest, but gets 250-300
> transactions /s. I really need advice on what is the fastest way to
> use VB6 and an OLEDB Provider. Since i need to work with millions of
> records I need a way to move them into the backend in minutes, not
> hours, days & months. Any help is appreciated
>
I think the fastest way is to contruct and external file (text fixed field
length) file, then declare the structure to the db as an external file, then
issue an INSERT * into INTTABLE select * from EXTTABLE
Alan
Yahoo! Groups Sponsor
ADVERTISEMENT
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]