从ORACLE读取的最快OLEDB [英] Fastest OLEDB read from ORACLE

问题描述

通过OLEDB从Oracle DB检索数据的最快方式是什么？

它应该是可移植的（必须在Postgres和MS SQL上工作）一个列被转移（来自某个大表的ID）。

当前性能为100k行/秒。

澄清：

datatable有23M条记录

查询是：SELECT ID FROM OBJECTS

Bottleneck是从oracle传送到客户端软件，

解决方案

这是什么，我会借机会。

修改：对于连接，我HEARTILTY推荐：

Oracle OLE for OLE ，简称OO4O。

这是Oracle的Oracle，而不是MS。它使用高性能本机驱动程序，不是ODBC的性能提升。我个人使用这个自己几次，它是快速。我连接到超大的数据库和数据仓库，每个表从不会少于200万条记录，大多数是更大的。

注意，你不需要知道OLE用这个。它包装OLE，因此名称。在概念和语法上，它将结果集包装到由SQL命令提供的动态集合中。如果你曾经使用DAO或ADO，你将在5分钟内生产。

这里有一个更深入的 文章。

如果您无法使用OO4O，那么Oracle制定的专业化.Net数据提供程序是非常好的。

---

使用一个WHERE子句？示例：从id =条件的对象中选择ID

WHERE

这只会在整个网络中发送感兴趣的记录。否则，所有2300万条记录都通过线路发送。

或，查看之间。

从对象中选择id，在此对象之间的id

BETWEEN

在您指定的范围内发送一组减少的记录。

HTH

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"

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

这篇关于从ORACLE读取的最快OLEDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！