从ASP.NET页面查询比控制台应用程序慢10倍（并重新排序结果！）。

使用Profiler比较执行计划。听起来好像使用了不同的
索引，因此也有不同的排序。

控制台应用程序的查询可能使用比ASP.NET更好的索引

（根本不可能使用或不使用索引）。

wi******@hotmail.com 写道：

Use Profiler to compare execution plans. It sounds like different
indexes are being used, hence the different ordering as well. The
Console app''s query may be using a better index than the ASP.NET one
(which may or may not be using an index at all).

wi******@hotmail.com wrote:

我有一大堆代码基本上是


IDbCommand cmd = db.CreateCommand（）;

cmd。 CommandText =" SELECT X，Y，Count（*）FROM Foo WHERE Z = 1 GROUP BY

X，Y&quot ;;

using（IDataReader reader = cmd.ExecuteReader （））

while（reader.Read（））

{

//从查询中获取值

} b / b
数据库是SQL服务器（MSDE 2000）和Foo数据库。拥有超过一亿美元的记录。但是查询只运行了大约12行。


当我将此代码编译到Windows控制台应用程序中并运行

时，它完成了下面的查询2秒。但是当我从ASP.NET类中运行完全相同的代码时，它需要超过20秒！

此外，即使返回的值完全相同，

行的顺序是完全不同的：具体来说，在ASP.NET下，

行全部按Y排序，然后按X排序，即使没有

他们应该是合乎逻辑的理由。我实际上尝试添加一个ORDER BY Y，

X到最后，这确实导致控制台应用程序版本以相同的顺序打印出

行，但对执行速度没有影响。


我已经尝试过使用OdbcConnection，OleDbConnection和

SqlConnection - 两者都没有任何区别。我已经确定

确保两者都是在发布模式下编译，然后重新启动IIS，并且确保我的ASP.NET应用程序除了这个之外没有其他任何操作br $>
查询。


欢迎任何建议！


谢谢


Dylan

I have a chunk of code which is essentially

IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SELECT X, Y, Count(*) FROM Foo WHERE Z = 1 GROUP BY
X, Y";
using (IDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
// grab values from query
}

The database is SQL server (MSDE 2000), and "Foo" has well over a
million records. The query however only runs about 12 rows.

When I compile this code into a Windows console application, and run
it, it completes the query in under 2 seconds. But when I run exactly
the same code from an ASP.NET class, it takes over 20 seconds!
Furthermore, even though the values returned are exactly the same, the
ORDER of the rows is quite different: specifically, under ASP.NET the
rows are all ordered first by Y then by X, even though there''s no
logical reason they should be. I actually tried adding an "ORDER BY Y,
X" to the end, which did cause the console app version to print out the
rows in the same order, but made no difference to the execution speed.

I''ve tried using an OdbcConnection, and OleDbConnection and an
SqlConnection - neither make any difference there either. I''ve made
sure both are compiled in release mode, and restarted IIS, and made
sure that my ASP.NET application is doing nothing else except this one
query.

Any suggestions most welcome!

Thanks

Dylan

无需交叉发布这么多！


它是否还需要20秒钟"第二"调用相同的ASP.NET

页面？


Jeff

< wi ****** @ hotmail。消息中写道

news：11 ********************** @ i3g2000cwc.googlegro ups.com ...

No need to cross-post so much!

Does it still take 20 seconds on the "second" invocation of the same ASP.NET
page?

Jeff
<wi******@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

>我有一大堆代码基本上是


IDbCommand cmd = db.CreateCommand（）;

cmd.CommandText =" SELECT X，Y，Count（*）FROM Foo WHERE Z = 1 GROUP BY

X，Y&quot ;;

using（IDataReader reader） = cmd.ExecuteReader（））

while（reader.Read（））

{

//从查询中获取值

}


数据库是SQL服务器（MSDE 2000）和Foo数据库。拥有超过一亿美元的记录。但是查询只运行了大约12行。


当我将此代码编译到Windows控制台应用程序中并运行

时，它完成了下面的查询2秒。但是当我从ASP.NET类中运行完全相同的代码时，它需要超过20秒！

此外，即使返回的值完全相同，

行的顺序是完全不同的：具体来说，在ASP.NET下，

行全部按Y排序，然后按X排序，即使没有

他们应该是合乎逻辑的理由。我实际上尝试添加一个ORDER BY Y，

X到最后，这确实导致控制台应用程序版本以相同的顺序打印出

行，但对执行速度没有影响。


我已经尝试过使用OdbcConnection，OleDbConnection和

SqlConnection - 两者都没有任何区别。我已经确定

确保两者都是在发布模式下编译，然后重新启动IIS，并且确保我的ASP.NET应用程序除了这个之外没有其他任何操作br $>
查询。


欢迎任何建议！


谢谢


Dylan

>I have a chunk of code which is essentially

IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SELECT X, Y, Count(*) FROM Foo WHERE Z = 1 GROUP BY
X, Y";
using (IDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
// grab values from query
}

The database is SQL server (MSDE 2000), and "Foo" has well over a
million records. The query however only runs about 12 rows.

When I compile this code into a Windows console application, and run
it, it completes the query in under 2 seconds. But when I run exactly
the same code from an ASP.NET class, it takes over 20 seconds!
Furthermore, even though the values returned are exactly the same, the
ORDER of the rows is quite different: specifically, under ASP.NET the
rows are all ordered first by Y then by X, even though there''s no
logical reason they should be. I actually tried adding an "ORDER BY Y,
X" to the end, which did cause the console app version to print out the
rows in the same order, but made no difference to the execution speed.

I''ve tried using an OdbcConnection, and OleDbConnection and an
SqlConnection - neither make any difference there either. I''ve made
sure both are compiled in release mode, and restarted IIS, and made
sure that my ASP.NET application is doing nothing else except this one
query.

Any suggestions most welcome!

Thanks

Dylan

Chris Lim写道：

Chris Lim wrote:

使用Profiler比较执行计划。听起来好像使用了不同的
索引，因此也有不同的排序。

控制台应用程序的查询可能使用比ASP.NET更好的索引

（根本不可能使用或不使用索引）。 br />

Use Profiler to compare execution plans. It sounds like different
indexes are being used, hence the different ordering as well. The
Console app''s query may be using a better index than the ASP.NET one
(which may or may not be using an index at all).

这没有意义 - 它是完全相同的数据库。


问题，相信它或不是，是这个词的情况是在

GROUP BY子句中。


如果使用大写''BY''，则查询需要20秒，如果您使用By，

它需要2秒钟。


非常接近我最近遇到的该死的最奇怪的虫子

最近，特别是因为它发生在所有三个IDbConnection

提供者之下（但不使用命令行osql工具）。

That wouldn''t make sense - it''s exactly the same database.

The problem, believe it or not, was the case of the word "by" in the
GROUP BY clause.

If you use uppercase ''BY'' the query takes 20 seconds, if you use "By",
it takes 2 seconds.

Pretty close to the damn weirdest bug I''ve come across any time
recently, especially because it happens under all three IDbConnection
providers (but not using the command-line osql tool).