从 ORACLE 读取最快的 OLEDB
通过 OLEDB 从 Oracle DB 检索数据的最快方法是什么？
它应该是可移植的（必须在 Postgres 和 MS SQL 上工作），只传输一列（来自某个大表的 ID）。
当前性能为 100k 行/秒。如果我想让它进展得更快，我是否期望太多？
澄清：
数据表有23M记录
查询是：SELECT ID FROM OBJECTS
瓶颈是从oracle到客户端软件的传输，客户端软件是c++/OLEDB
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
算什么，我就去碰碰运气。
编辑：就连接而言，我衷心推荐：
Oracle Objects for OLE，简称 OO4O。
它是由 Oracle 为 Oracle 而制造的，而不是由 MS 制造的。它使用高性能本机驱动程序，而不是 ODBC 来提高性能。我自己曾多次使用过这个方法，而且速度很快。我连接到非常大的数据库和数据仓库，其中每个表都不少于 200 万条记录，大多数都大得多。
请注意，您无需了解 OLE 即可使用此功能。它包装了 OLE，因此得名。从概念上和语法上讲，它将“结果集”包装到由 SQL 命令提供的动态集中。如果您曾经使用过 DAO 或 ADO，您将在 5 分钟内提高工作效率。
这是一篇更深入的文章。
如果您不能使用OO4O，那么Oracle专门做的.Net Data Provider就非常好。不是MS制造的。
HTH
使用“WHERE”子句？示例：“从 id = criteria 的对象中选择 id”
WHERE
这仅通过网络发送感兴趣的记录。否则，所有 2300 万条记录都会通过网络发送。
或者，看看“之间”。
“从 id 介于 thisone 和 thatone 之间的对象中选择 id”
BETWEEN
发送您指定范围内的减少的记录集。
华泰
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