SQL performance issue

[Kai77]

I made a survey webapp using Asp.Net. The answers given by a user are stored in a table called "survey_results". About 10 stored procedures are called each time a page of the survey is viewed. The stored procedures are simply a query (e.g.: select * from survey_results where questionid = '1' and subquestionid='2') returning a couple of rows (1 to 5).

The webapp has been running for 2 months now and currently the table holds about 150000 rows. The last few days the webapp has been very very slow when going from one page to another webpage. I feel that it has something to do with the database(server). Any ideas?

 

[vongrunt]

> I feel that it has something to do with the database(server).

From short description you gave this is quite possible - assuming that webapp was probably designed/tested on very small tables. But personally I wouldn't exclude web server/link as possible bottlenecks.

Can you run profiler and see what's going on?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Kai77]

Profiler show about 12-16 rows in total when accessing a page. All of them just runs the stored procedure and resets the connection immediately afterwards. But I ran profiler locally in my own testing environment, it's the online webapp that has the problem. The hosting provider told me that the SQL server never reaches a significant heavy load, so I will just have to trust his word on it.

 

[vongrunt]

Rows... exactly to which profiler data column are you referring to?

Also: does testing environment have approximately the same number of records as production database?



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Kai77]

No, the testing database does not even come close to the production database. I will make a copy of the production database and test it locally on Monday.

Thanks for your insights.
Enjoy your weekend!

 

[vongrunt]

OK, feel free to post results here next week.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)