为什么datatable的load方法有时候会很慢？

Question

该项目是一个ASP/VB.net的Web应用程序。问题在于一些页面非常慢。在尝试找到瓶颈后，发现当用查询结果填充datatable时，load方法是问题所在。

我们使用Oracle数据库，并且查询是在存储过程中执行的。例如，我们有一个相对简单的select语句在一个过程中返回2列6行，确定执行时间约为0.015秒。然而，将OracleDataReader加载到datatable中平均需要7秒钟 - 对于这样一个小的记录集来说，这是一个荒谬的时间。经过对查询进行调整，我发现一个简单的decode语句似乎是引起问题的原因。decode语句的使用方式如下：

WHERE DECODE(iBln, 1, column1, column2) BETWEEN iDate1 and iDate2

iBln变量只是传递一个数字作为布尔变量，以确定哪个列应该在两个日期之间。如果我注释掉这个decode语句，并使它变成"column1 BETWEEN iDate1 and iDate2"，那么load方法就不需要任何时间，这表明确实是decode语句引起了问题。

所以我只是希望能听到任何人对导致这个问题的原因或如何解决它有什么想法。这是一个简单的decode，它怎么会影响load方法呢？

- Nick

Answer 1

我建议您验证一下column1和column2是否存在索引。如果有，那么问题很可能是DECODE函数导致无法使用这些索引。您可以尝试按照以下方式进行重写：

WHERE ( ( iBin = 1 AND column1 BETWEEN iDate1 AND iDate2)
        OR
        ( (iBin IS NULL OR iBin <> 1) AND column2 BETWEEN iDate1 AND iDate2)
      )

- Dave Costa

---

非常棒！我也向Justin Cave提出了这个问题，因为我仍然很好奇 - 您知道为什么DECODE会导致索引在使用存储过程时无法使用，但是当我在查询工具中单独运行查询时，索引完全正常？ - Nick

@ibar98：当您交互式地执行查询时，是否为iBin硬编码了一个值？如果是这样，那么解析器可能已将DECODE表达式解析为单个列名，并因此使用索引访问路径。 - Dave Costa

啊，是的，我确实硬编码了这个值。我只是没有意识到解析器会走得那么远来优化查询。感谢你的所有帮助！ - Nick

Answer 2

如果你的存储过程返回了REF CURSOR，则在存储过程中打开游标将非常快，无论你执行的查询是什么。打开游标不需要Oracle执行实际运行查询的任何工作，它只需要Oracle确定查询计划，这应该是几乎瞬间完成的。

在类似SQL * Plus的工具中，从REF CURSOR提取数据需要多长时间？如果需要接近7秒钟（正如我怀疑的那样），则可以排除OracleDataReader类作为问题源。在这种情况下，问题几乎肯定是查询计划效率低下。

根据您的描述，我的猜测是column1被索引了，column2可能也被索引了，但不确定。但是，普通索引在column1或column2上都不能用于评估涉及到DECODE函数调用的谓词。如果没有其他对索引列的谓词，那可能会强制Oracle在底层表上执行全表扫描（发布完整查询、表定义和查询计划将有助于解决问题）。

- Justin Cave

---

这绝对是有道理的，我进行了一些测试，证明我们的测试程序确实将负载方法归咎于查询运行所花费的时间。然而让我困惑的是，当我将选择语句从存储过程中取出（带有解码函数的存储过程），并在我们的查询工具中运行它（公司使用Benthic Golden6），它执行得非常快且完美。是否有某种原因使DECODE在从存储过程中调用时会使索引失效，但在直接查询时不会？ - Nick

@ibar98 - 你在这里描述的是与你回答Dave问题时相同的情况吗？那时iBin的值是硬编码的（因此允许Oracle在解析时评估DECODE）？还是这是另一个测试？ - Justin Cave

是的，这是相同的情况，现在一切都更加清晰了。你和戴夫非常有帮助，我真的很感激你们给我的所有帮助。 - Nick