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
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"
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"
That sends a reduced set of records in the range you specify.
HTH