hello all first

so i'll try to explain what's going on to see if anyone of you have any idea what's wrong coz nothing should be wrong but still it is very wrong :)

i'm working on a rather big website project (some movie database you can visit here : www.hkcinemagic.com) so i consider myself an experienced developper in ASP / MSSQL2k and recently i've experienced some nasty problems with simple pieces of code that shouldn't have made any problems at all..

what i can add to my intro is that all the pieces of code work, both on the ASP side and the SQL side, the problem is a dramatic performance loss when everything tries to work together..

so, the thing is pretty simple, i have to make some lists of movies and stuff based a few parameters, filter parameters and sorting parameters..
The whole thing is built in a quite huge stored procedure in order to avoid having msqql compile every different query that can come from every combination of parameters, on the fly.. the stored procedure is very basic, a few "if then else" and the queries and it works perfect.

the stored procedure returns a single recordset.

1st : i've texted it in Query Analyzer with a large amount of different combinations of parameters and everytime i get the results in the blink of an eye.. win2k task manager shows a short cpu peak around 20-50%.
2nd : i've extracted one of the query from the stored procedure, a large one, and applied it to my list asp page.. The page loads quite fast and i just get a slightly bigger cpu peak never reaching 100% no matter how big the results list is (100 to 200 movies)
the code i use is almost the same everywhere in my asp pages :
sql contains my query, conn is of course a properly initialised ado connection and rs an ADO Recordset

now here comes the troubles

3rd : i use my stored proc into the asp code
anyway i try to use it i get the same performance problem.
sql contains the "exec storeproc 0,1,2,5,4,7,....params" command
if i declare the command in a more classic way i get the exact same problem so i won't put the code here..

when i use that asp ado command method, which one i use in many places in other scripts, mssql goes crazy and starts to load the CPU to 100% for 10 or 20 seconds..
And i really want to stress out that it's sql server that messes things up as IIS does nothing and waits for MSSQL to finish its work..
I also tried to simply call that stored procedure from an empty asp script to make sure that nothing is done by asp/iss and i get the same performance problem..


i've probably reached a frontier or some kind of bug but i can't figure out a way around..

I encountered another performance problem a few weeks ago which is a serious asp limitation in string/arrays manipulation but i could easily find a way around in my asp code..

unfortunately i have no idea what goes wrong with mssql server and i don't see how i could simplify my stored procedure which is very basic already..

any idea would be really appreciated..

if you know other places where i could try to get some help, i'd be glad to ask my question there..

thx for reading :)

Marc