|
|
You're in the
sql server msde
group:Query 10 times slower (and results reordered!) from ASP.NET page than console app.
sql server msde:
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
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). [quoted text, click to view] 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
No need to cross-post so much!
Does it still take 20 seconds on the "second" invocation of the same ASP.NET page? Jeff [quoted text, click to view] <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 >
[quoted text, click to view] Chris Lim wrote: That wouldn't make sense - it's exactly the same database.> 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). > 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).
[quoted text, click to view]
> wasn't the only thing causing the problem. I have to admit I didn't > try chaning the case of other parts of the query, but it was absolutely > consistent: I could change it to "BY", run it 3 times, and have it take > 20 seconds each time, then change it to "By" and it would run at < 2 > seconds. You could run BY 8000 times and By 8000 times and it would still execute the same in each case. Unless you follow my earlier advice to clear the procedure cache and drop the buffers, which will get rid of both plans and you may see that the behavior was due to a bad plan stored for one of the cases, not due to a bug in ODBC or due to the case itself. A
[quoted text, click to view]
> wasn't the only thing causing the problem. I have to admit I didn't > try chaning the case of other parts of the query, but it was absolutely > consistent: I could change it to "BY", run it 3 times, and have it take > 20 seconds each time, then change it to "By" and it would run at < 2 > seconds. You could run BY 8000 times and By 8000 times and it would still execute the same in each case. Unless you follow my earlier advice to clear the procedure cache and drop the buffers, which will get rid of both plans and you may see that the behavior was due to a bad plan stored for one of the cases, not due to a bug in ODBC or due to the case itself. A
[quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote: Might be, but my additional investigation seemed to reveal that it was> >> 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). > >> > > 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. > > That's not exactly why. And I think Chris was more on track than you might > think "makes sense." > > If you clear out the procedure cache and the buffers, you will find that > whether you use By or BY the first time makes no difference. The problem is > that SQL Server stores execution plans for ad hoc SQL statements, and treats > the plan with case sensitivity (and even for whitespace differences). So it > might be that when you ran one version you got a certain plan because of > data size or who knows what, and when you ran a version with a different > casing, a different plan got stored (again, who knows why, we don't know > enough about your system). > a bug in the Odbc and OleDb providers, given that the case of "By" wasn't the only thing causing the problem. I have to admit I didn't try chaning the case of other parts of the query, but it was absolutely consistent: I could change it to "BY", run it 3 times, and have it take 20 seconds each time, then change it to "By" and it would run at < 2 seconds. [quoted text, click to view] > There's a guaranteed way around this: STOP USING AD HOC SQL. Is there any > reason you're not using stored procedures? Yup, we need to support Access, Oracle, MySql etc. etc. I certainly want to look at using more parameterized views, which would probably help in this case. But I actually build up the SQL statements dynamically based on a number of user-configurable parameters - including even which fields are selected/grouped on, so it's hard to see how to avoid some amount of ad hoc SQL. [quoted text, click to view] > > Another way around this is to use consistent casing and spacing/indenting > for T-SQL keywords. You'd be amazed how much more readable and maintainable > your code will become if you pick a convention and stick with it. I always use all upper-case - I've actually no idea how I ended up with the mixed case "By", but it was a good job I did, or I might never have found the problem.
[quoted text, click to view]
> 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. [quoted text, click to view] >: 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. [quoted text, click to view] > 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
[quoted text, click to view]
wizofaus@hotmail.com wrote: > Might be, but my additional investigation seemed to reveal that it was > a bug in the Odbc and OleDb providers, given that the case of "By" > wasn't the only thing causing the problem. I have to admit I didn't > try chaning the case of other parts of the query, but it was absolutely > consistent: I could change it to "BY", run it 3 times, and have it take > 20 seconds each time, then change it to "By" and it would run at < 2 > seconds. Strange problem. Still, using Profiler you could at least see where the delay is and it might give some more clues as to what's actually going on.
[quoted text, click to view]
>> 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). >> > 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. That's not exactly why. And I think Chris was more on track than you might think "makes sense." If you clear out the procedure cache and the buffers, you will find that whether you use By or BY the first time makes no difference. The problem is that SQL Server stores execution plans for ad hoc SQL statements, and treats the plan with case sensitivity (and even for whitespace differences). So it might be that when you ran one version you got a certain plan because of data size or who knows what, and when you ran a version with a different casing, a different plan got stored (again, who knows why, we don't know enough about your system). There's a guaranteed way around this: STOP USING AD HOC SQL. Is there any reason you're not using stored procedures? Another way around this is to use consistent casing and spacing/indenting for T-SQL keywords. You'd be amazed how much more readable and maintainable your code will become if you pick a convention and stick with it. A
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |