为什么数据表的load方法有时这么慢？
该项目是 ASP/VB.net 中的 Web 应用程序。问题是有些页面速度慢得令人难以置信。在尝试查找瓶颈后，发现是用查询结果填充数据表时的加载方法。
我们使用 Oracle 数据库，查询在存储过程中执行。例如，我们在一个过程中有一个相对简单的 select 语句，该语句返回 2 列 6 行，确定执行时间约为 0.015 秒。然而，将 OracleDataReader 加载到数据表中平均需要 7 秒 - 对于如此小的记录集来说，这是一个荒谬的时间。在搞乱查询后，我发现一个简单的解码语句似乎导致了问题。解码语句的使用类似于如下：
WHERE DECODE(iBln, 1,column1,column2) BETWEEN iDate1 和 iDate2
iBln 变量只是一个传入的数字，用作布尔变量，用于确定哪一列应位于两个日期之间。如果我注释掉这个解码语句并将其简单地设置为“iDate1 和 iDate2 之间的第 1 列”，那么加载方法根本不需要花费任何时间，这表明它确实是解码语句导致了问题。
因此，我只是希望能听到任何人的意见，了解导致此问题的原因或如何解决它。这是一个简单的解码，它如何影响加载方法呢？
The project is a web app in ASP/VB.net. The issue is that some pages are mind-boggingly slow. After trying to track down the bottleneck, it was discovered to be the load method when filling a datatable with query results.
We are using an Oracle database and queries are executed in stored procedures. As an example, we have a relatively simple select statement within a procedure which returns 2 columns with 6 rows which was determined to take about 0.015 seconds to execute. However it takes on average 7 seconds to load the OracleDataReader into a datatable - a ridiculous amount of time for such a small record set. After messing around with the query, I found that a simple decode statement appeared to be causing the issue. The decode statement is used similar to as follows:
WHERE
DECODE(iBln, 1, column1, column2) BETWEEN iDate1 and iDate2
The iBln variable is simply a number being passed in to act as a boolean variable for determining which column should be between two dates. If I comment this decode statement out and make it simply "column1 BETWEEN iDate1 and iDate2" then the load method takes no time at all as it should, signifying that it is indeed the decode statement causing issues.
So I'm just hoping to hear from anyone that could have an idea as to what's causing this or how to fix it. It's a simple decode, how does it even affect the load method anyway?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将验证列 1 和列 2 是否存在索引。如果是这样，可能的问题是 DECODE 阻止了索引的使用。尝试重写为：
I would verify that indexes exist for column1 and column2. If so, the likely problem is that the DECODE is preventing the use of the indexes. Try rewriting as:
如果您的存储过程返回 REF CURSOR，则无论您正在执行什么查询，在存储过程中打开游标都会非常快。打开游标并不要求 Oracle 执行任何实际运行查询的工作，它只要求 Oracle 确定应该或多或少是瞬时的查询计划。
在 SQL*Plus 之类的工具中从 REF CURSOR 获取数据需要多长时间？如果花费了接近 7 秒的时间（我怀疑它会），则可以消除 OracleDataReader 类作为问题根源。在这种情况下，问题几乎肯定是查询计划效率低下。
根据您的描述，我的猜测是
column1已建立索引。column2也可能被索引，目前还不清楚。但是column1或column2上的常规索引无法用于评估涉及调用DECODE函数的谓词。如果索引列上没有其他谓词，则可能会强制 Oracle 对基础表进行表扫描（发布完整查询、表定义和查询计划会很有帮助）。If your stored procedure is returning a REF CURSOR, opening the cursor in the stored procedure will be very fast regardless of the query you're executing. Opening a cursor doesn't require that Oracle do any of the work of actually running the query, it just requires that Oracle determine the query plan which should be more or less instantaneous.
How long does it take to fetch the data from the REF CURSOR in something like SQL*Plus? If it takes something close to 7 seconds (as I suspect it will), you can eliminate the OracleDataReader class as the source of the problem. In that case, the problem would almost certainly be that the query plan is inefficient.
Based on your description, my guess is that
column1is indexed.column2may also be indexed, it's not clear. But a regular index on eithercolumn1orcolumn2could not be used to evaluate the predicate that involves the call to theDECODEfunction. If there are no other predicates on indexed columns, that may force Oracle to do a table scan on the underlying table (posting the full query, the table definition, and the query plan would be helpful).