My query times are super slow - any suggestions?

I have a hosted SQL Server 2005 database through discount ASP.NET.

My web application has a page that executes a stored procedure and returns a data set that I read into a Repeater control.

Here's what I'm not understanding - when I log into the database using SSMS and execute the stored procedure, a query with 160 results executes in 1 second or less. When my web page executes the stored procedure, the execution time is anywhere from 20-23 seconds!

Any ideas about how to improve the performance here? Asfar as I can tell, I'm following best practices on the database side (stored procedure, only returning necessary rows, no select * happening anywhere, referencing an indexed view, tables are all indexed and have primary keys, etc.).At first I thought it was some inefficiency on the ASP.NET/UI side, but I modified the stored procedure to start logging to another table performance info (when the query starts, stops, number or results returned) and am getting some really poor performance - 8 rows returned PER SECOND is terrible.

Thanks,

Eric

 

Well, I've been working on this with some guys through the tech support, and nothing obvious.

However, after all of this activity, the performance has actually gotten better. The same web page is now retrieving hundreds of rows in a second or two. I created a very simple test page that just calls the stored procedure, obtains a SqlDataReader and iterates over the reader and spits out list item HTML to a page - it's super fast. And the main application page is just as fast.

This is really weird, because my web app has been slow for months and I always thought it was on the application side. I'm wondering if all of this activity has cleared up some connection pooling on the db server or something? It's totally weird - I'm going to keep my eye on it and check on the app page tomorrow and see if gets painfully slow again.

One thing I did notice was that I had set the page directive's Debug="true" before and changed it to false. Also, the web.config was set with the debug=true. Not sure that it will make a huge difference, but I turned all the debugging off, hopefully that should speed things up as well.

I also went over the code behind again - I'm not leaving behind any undisposed objects or open connections or anything that could slow my app down.

 

Good job...You should get a bonus award for posting the end result as you did.

 

Thanks for the feedback! I find myself relying more and more on various forums and blogs for almost everything technical these days, so I feel like I should give something back in some way....I just hope this can help someone else some day.

 

OK, I figured it out. It is really an odd bit of SQL Server stored procedure optimization that I've never run up against before.

In a nutshell, the culprit was using the @@ROWCOUNT call to return the number of rows returned by the select statement inside of my stored procedure. I've never had problems using this before, but that's what seems to have caused it.

To test this, I wrote a simple .NET console application that ran two tests: 1) a dynamic SQL query that used the same SELECT statement inside my stored procedure, and 2) a call to the stored procedure that the web form is using. The dynamic SQL executed and returned its data to my console app in a little over 1 second. The stored procedure took anywhere between 12-30 seconds! I would have the datareader spit the rows out to the console as it read them, and it was just chunking it up in batches with a second's pause between every handful of records when I called the stored procedure.

I went into the stored procedure and commented out the non-SELECT statement code (the rest of the stored procedure is doing an insert into a different table), saved it, and tried it again. The stored procedure executed in less than 1 second.

I needed to get a row count because I want to log how many results are coming back from these queries to monitor it. I had been using @@ROWCOUNT to do this, and storing the value in a locally declared variable. In the end, I ended up using a second call variation of the SELECT statement but using a COUNT() on the result set to get the row count instead of using @@ROWCOUNT. This adds a little more time to the query -- now the stored procedure executes in a little over 1 second -- but it's negligible.

If anyone's curious, this is basically what the T-SQL inside the stored procedure looked like before and after.

BEFORE -----------------------

 

Actually, I spoke way too soon. What I thought was a fix was just a false positive. I'm back to square frustrated.


I started logging the execution time of this stored procedure (inside SQL Server) so that everytime it's called, it writes its start and end time to a table. I also wrote a custom console app to call this stored procedure from my server at home to make sure the responsiveness falls within an acceptable threshold. It was doing fine for most of the day, and then at the end of the day it suddenly went from executing in a second or two to anywhere between 30 and 80 seconds! There's an excerpt of these times below.


When I caught that the times logged in the db were too long, I go to the live web app and try hitting the pages that use the stored procedure, and they are indeed super slow.


Basically, by the time I catch it and ask for help, by the time anyone gets around to verifying or trying to recreate my problem, it appears to be working again. When the database log times are fast again, when I check the web page they are fast. I really am at a loss.


The most crazy making part about this is that when I open SQL Server Management Studio and execute the stored procedure withing that client, the stored procedures execute as expected (under a second). So for some reason other client applications (the web app, the console app) are being penalized somehow?


As you can see below, it's doing fine all day until around 5:30 pm, the response time ramps up from ~1 second to 10 seconds then at 5:40pm it's 80 seconds! Then it just was terrible for a half hour then it's good again. I have to wonder what's going on with the database server...




<COLGROUP>
<COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6473" width=177>
<COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153>


dateSearched
elapsedTimeInSeconds

10/2/08 10:00 AM
2.423

10/2/08 10:01 AM
0.373

10/2/08 11:31 AM
0.623

10/2/08 11:33 AM
0.47

10/2/08 11:34 AM
0.296

10/2/08 11:34 AM
1.686

10/2/08 11:34 AM
0.123

10/2/08 11:35 AM
0.296

10/2/08 11:35 AM
0.36

10/2/08 11:37 AM
2.593

10/2/08 1:20 PM
0.3

10/2/08 1:23 PM
4.703

10/2/08 1:24 PM
2.19

10/2/08 1:26 PM
0.45

10/2/08 1:26 PM
0.703

10/2/08 1:45 PM
0.783

10/2/08 2:03 PM
0.673

10/2/08 2:16 PM
0.78

10/2/08 2:19 PM
1.733

10/2/08 2:20 PM
1.296

10/2/08 2:47 PM
0.703

10/2/08 3:23 PM
0.533

10/2/08 3:38 PM
1.14

10/2/08 4:53 PM
1.64

10/2/08 5:29 PM
10.563

10/2/08 5:40 PM
88.326

10/2/08 5:41 PM
35.03

10/2/08 5:42 PM
71.31

10/2/08 5:43 PM
35.436

10/2/08 5:44 PM
27.75

10/2/08 5:44 PM
29.966

10/2/08 5:45 PM
31.153

10/2/08 5:46 PM
29.483

10/2/08 5:46 PM
29.703

10/2/08 5:47 PM
33.703

10/2/08 5:49 PM
32.326

10/2/08 5:50 PM
30.326

10/2/08 5:50 PM
30.966

10/2/08 5:51 PM
28.593

10/2/08 5:51 PM
31.236

10/2/08 5:52 PM
30.36

10/2/08 5:52 PM
35.373

10/2/08 5:53 PM
31.36

10/2/08 5:54 PM
22.653

10/2/08 5:55 PM
25.203

10/2/08 5:55 PM
36.406

10/2/08 5:56 PM
36.033

10/2/08 5:56 PM
34.326

10/2/08 5:58 PM
28.296

10/2/08 5:58 PM
28.533

10/2/08 5:59 PM
30.263

10/2/08 5:59 PM
32.5

10/2/08 6:01 PM
0.716

10/2/08 6:04 PM
0.783

10/2/08 6:04 PM
0.346