Oracle DataReader in .Net中的大性能问题 [英] Big Performance Problems With Oracle DataReader in .Net

问题描述

我有几个Oracle程序生成/返回大量的数据，我需要写出到一个文件。我目前正试图用数据读取器完成。它似乎是工作，我已成功生成一个479mb文件没有任何麻烦。从我检索dataReader完成文件的时间不到4分钟。

I have a few Oracle procedures that generate/return a large amount of data that I need to write out to a file. I'm currently trying to accomplish with a data-reader. It seems to be working, I've successfully generated a 479mb file without any trouble. It took less than 4 minutes from the time I retrieved the dataReader to complete the file.

但是我得到一个特定过程的dataReader是抓取。这是令人难以置信的慢。我修改了我的代码，尝试并更好地了解发生了什么....

But the dataReader I get for a particular procedure is crawling. It's unbelievably slow. I modified my code to try and get a better idea of what is going on....

System.Diagnostics.Debug.Write("Performing .Read() on DataReader: ")
Dim d1 As DateTime = DateTime.Now
Dim result As Boolean = myDataReader.Read()
Dim ts As TimeSpan = DateTime.Now.Subtract(d1)
System.Diagnostics.Debug.WriteLine(ts.ToString)

有趣的是，我的输出结果如下：

The interesting this is that my output ends up looking like this:

Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:00:00
Performing .Read() on DataReader: 00:07:33.5037500

我真的很失去下一步做什么。我看不到任何独特或不同的行需要07：33.5037500。有任何建议吗？

I'm really at a loss for what to do next. I can't see anything unique or different about the row that takes 07:33.5037500. Any suggestions?

编辑：

感谢大家的回应。首先，尽我所能地告诉，没有异常被抛出。正如所建议的，我已经看看这个特殊的程序，展示了上述的行为，而程序是可笑的大规模;但它看起来像使用大量的光标来填充oracle临时表。返回的Ref游标是一个SELECT * FROM的临时表。

Thanks for the responses everyone. First, as best as I can tell, no exceptions are being thrown. As suggested, I've taken a look at this particular procedure that is exhibiting the behavior above and while procedure is ridiculously massive; but it looks like it uses a lot of cursors to populate an oracle temp table. The Ref Cursor that is returned is a SELECT * FROM that temp table.

我正在写一个PL / SQL块，打开该游标，看看性能问题存在当我删除.Net代码....希望有帮助;但如果你有任何额外的想法，将非常感激。

I'm writing a PL/SQL block that will open that cursor to see if the performance issue exists when I remove the .Net code....hopefully that will help; but if you've got any additional thoughts, it will be much appreciated.

再次感谢。这似乎是一个PL / SQL问题，而不是一个.NET问题。

Thanks once more. This does appear to be a PL/SQL issue and not a .NET problem.

推荐答案

数据库实际上在做什么？

What is the database actually doing ?

GROUP BY或ORDER BY可能需要生成完整的结果集，然后在返回行之前对其进行排序/聚合。查询扫描大型表格可能会在第一对块中找到50行，然后再读取另外十万个块，然后再找到另一个块。

A query with a GROUP BY or and ORDER BY may need to generate the full result set, then sort/aggregate it before returning a row. A query scanning a large table may find 50 rows in the first couple of blocks, then read another hundred thousand blocks before it finds another one.

我建议您忽略VB代码并发布数据库代码。

I suggest you ignore the VB code and post the database code.

这篇关于Oracle DataReader in .Net中的大性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！