业绩不佳让CLOB字段从Oracle在.net [英] Poor performance getting clob field from Oracle in .Net

问题描述

我想读从Oracle .NET中CLOB列并观察表现非常差，大量的网络流量。

I am trying to read a clob column from oracle in .Net and observing very poor performance and lots of network traffic.

我已经试过ODP + OCI，devArt + OCI方式与相同的结果访问数据 - 它需要大约20秒获得1000行数据读取器和读取的每一行的CLOB值。

I have tried ODP+OCI, devArt+OCI ways to access the data with the same results - it takes around 20 seconds to get 1000 rows in a data reader and read the clob value for each row.

检查Wireshark的痕迹，事实证明，每一次我尝试读取CLOB字段为读者单列，有客户端和服务器之间发送的附加多个TCP数据包。因此，对于1000行这成为比如果查询只有一行慢1000倍。

Examining wireshark traces, it turns out that every time I try to read the clob field for a single row in the reader, there are additional multiple tcp packets sent between client and server. So for 1000 rows this becomes 1000 times slower than if querying just one row.

与此同时，如果我运行在SQL Developer中相同的查询（我相信使用瘦JDBC驱动程序而不是OCI），我得到的结果瞬间，包括CLOB值。它不尝试查询CLOB的每一行 - ！这让他们都在同一个走

At the same time, if I run the same query in SQL Developer (which I believe uses thin jdbc driver instead of oci), I get the results instantly, including the clob values. It does not try to query clob for each row - it gets them all in one go!

不过，我没有看到一个瘦客户机.NET。我怎么能加快速度？请帮帮忙！

But I don't see a thin client for .net. How can I speed things up? Please help!

编辑：我的字段类型实际上是XMLType的存储为CLOB，而不是一个真正的CLOB。使用getClobVal超过它改善了结果由50％至10秒为1000行。但在同一时间，SQL开发人员的返回结果瞬间，没有在10秒内。

My field type is actually XMLTYPE stored as clob, not a true clob. Using getClobVal over it improves the result by 50% to 10 seconds for 1000 rows. But at the same time, Sql Developer is returning results instantly, not in 10 seconds.

推荐答案

您已经正确地指出甲骨文延迟的LOB检索。因此，性能应用程序由网络往返时间的限制。

You have correctly observed that Oracle delays the retrieval of LOBs. Thus, the performance your application is limited by the network round-trip time.

随着的OracleCommand 类的<​​code> InitialLOBFetchSize 属性（在ODP.NET，看到的文档），你可以告诉甲骨文检索LOB的一部分，最初检索该行。如果您的LOB都不会太长，这可以使完全不同。

With the InitialLOBFetchSize property of the OracleCommand class (in ODP.NET, see documentation), you can tell Oracle to retrieve part of the LOB with the initial retrieval of the row. If your LOBs aren't too long, this can make quite a difference.

这篇关于业绩不佳让CLOB字段从Oracle在.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！