Microsoft SQL Server Desktop Engine - Query 10 times slower (and results reordered!) from ASP.NET page than console app.

Author

Query 10 times slower (and results reordered!) from ASP.NET page than console app.

wizofaus@hotmail.com

2006-10-25, 6:04 am

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

2006-10-25, 6:04 am

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).

wizofaus@hotmail.com wrote:
> 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

Jeff Dillon

2006-10-25, 6:04 am

No need to cross-post so much!

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

Jeff
<wizofaus@hotmail.com> wrote in message
news:1161731295.801956.190640@i3g2000cwc.googlegroups.com...
>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
>

Aaron Bertrand [SQL Server MVP]

2006-10-25, 6:04 am

> Furthermore, even though the values returned are exactly the same, the
> ORDER of the rows is quite different

Well, you don't have an ORDER BY clause, so SQL Server is free to return the
rows in any row it chooses. There is nothing wrong with even the same piece
of code running multiple times and returning a different order every time...
neglecting to have an ORDER BY clause is basically telling SQL Server that
you don't care about the order. So, long story short, if you want/expect a
certain order, SAY SO.

>: 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.

You could say the same for the ordering no matter what order they came out:
without an order by clause, there is no logical orderto expect. SQL Server
will simply return the rows in whatever order it deems most efficient.

> sure both are compiled in release mode, and restarted IIS

And are you only testing the ASP.Net page once after restarting IIS? See,
the first time you load an ASP.Net page in IIS, it does a bunch of
background activity with it. Running Profiler might point out how much of
that time IIS is spent doing its work with the ASP.Net file, as opposed to
running the query.

Remember too that there is more overhead in the display time used by the
ASP.Net page to render results to HTML than there would be for a console app
returning plain text to the command line. And if you are running the
console app on the same machine as SQL Server, and the web site is on a
different machine, you need to factor network latency in there as well.

A

Sponsored Links