Hi
I am running Web server(iis 5.0) and database server( sql server 7) on a single pc(IBM , 512 P4 MB RAM). The application i run has a stored procedure which when called , starts a while loop of abt 100000 records. In the loop based on conditions checked other stored procedures are called. This leads to processor utilisation of abt 80-98%.
The front end is in ASP 3.0. When i click from the front end the procedure are called.However the speed at which the process runs when called from front end compared to when it is run as a standalone from SQL server is very slow.Some times the database server gets hung. This happens only when run from front end.
Why is this difference is performance. Will this improve if IIS and sql server are seperated.
While the stored procedure runs ,the front end has to wait for it to get over. This takes abt an hour or more. I hve kept server script timeout to 2 hrs. Yet it gets timedout sometimes.. can i run some dummy process in front end meanwhile?
pls suggest

Do you have any indexes on the table(s) your SP is querying?

The table size with 100,000 rows of data is not very big. The time of the execution of SP depends on the complication of the queries in it. If you can use the index on the table, that will improve a lot. The performance for the case in which both web server and database server are on the same computer should be better than the one for the situation with separated servers of IIS and SQL since the latter involves network access.

HI
I am using index on the table.. There is a clustered index as well as a non clustered indes.. The queeries are complex.. in the sense that.. for each iteration of the loop.. the main table is queried.. a record no fetched.. then another stored procedure is called with the record no as input( the stored proc called is based on a if condition checked in the iteration).. The SP called itself are complex and hve lot of table fetches..
I hve been trying to tune these procs.. and hence used indexes on all tables..
how do i chk if the SP is using those indexes or not?