1

What would be the fastest way of retrieving data from the Oracle DB via OLEDB?

It should be portable (have to work on Postgres and MS SQL), only one column is transfered (ID from some large table).

Current performance is 100k rows/sec. Am I expecting too much if I want it to go faster?

Clarification:

datatable has 23M records

Query is: SELECT ID FROM OBJECTS

Bottleneck is transfer from oracle to the client software, which is c++/OLEDB

Daniel Mošmondor
  • 19,718
  • 12
  • 58
  • 99
  • 1
    Your question is somewhat under-detailed. How does your client consume these rows? Does sort order matter? How many rows in total? Is the executed query complicated? Does network traffic have an impact? Does disk i/o have an impact? – APC Aug 17 '10 at 13:01
  • Agree with APC. There is no faster way than this simple select statement, but other variables may have an impact. – rkellerm Aug 17 '10 at 14:27
  • Use a "WHERE" clause? select id from objects where id = criteria. OR, look into "between" – JustBoo Aug 17 '10 at 18:04
  • Guys, I'm talking about TECHNOLOGY for fastest transfer with at least overhead for taking data from the database. I need ALL data, query is arbitrary anyway. Today there is 23M records, tomorrow will be 23B records. I need ezoterics here, for example Oracle stored procedure that will LZW data on one end and output it as a single blob result. – Daniel Mošmondor Aug 18 '10 at 07:58
  • A GigaBit (or higher) network connection with gigabit switches... and perhaps connected to a server ran by God? :-) – JustBoo Aug 18 '10 at 18:22
  • JustBoo - there still remains the question of choosing the right connectivity API. :) – Daniel Mošmondor Aug 19 '10 at 09:20
  • See new verbiage added to my post. – JustBoo Aug 19 '10 at 15:31

1 Answers1

1

What the heck, I'll take a chance.

Edit: As far as connectivity, I HEARTILTY recommend:

Oracle Objects for OLE, OO4O for short.

It's made by Oracle for Oracle, not by MS. It uses high-performance native drivers, NOT ODBC for a performance boost. I've personally used this myself on several occasions and it is fast. I was connecting to extremely large DB's and data warehouses where every table was never less than 2 million records, most were far larger.

Note you do not need to know OLE to use this. It wraps OLE, hence the name. Conceptually and syntactically, it wraps the "result set" into a dynaset fed by SQL commands. If you've ever used DAO, or ADO you will be productive in 5 minutes.

Here's a more in-depth article.

If you can't use OO4O, then the specialized .Net Data Provider made by Oracle is very good. NOT the one made by MS.

HTH

Use a "WHERE" clause? Example: "select id from objects where id = criteria"

WHERE

This sends only the record of interest across the network. Otherwise all 23 million records are sent across the wire.

OR, look into "between."

"select id from objects where id between thisone and thatone"

BETWEEN

That sends a reduced set of records in the range you specify.

HTH

JustBoo
  • 1,723
  • 9
  • 9