one ado connection object - multiple spids?

Hi,
We have asp application (ado connection) which connects to the sql server
2000 (sp3). There is one asp page which sequentially executes set of 6
stored procedures. When I execute same set of stored procedures in query
analyzer, I get response in less than 1 second.When application does that,
response gets back in 15-20 seconds (only when we have problem, otherwise in
2-3 seconds). In the trace, I noticed that each of these 6 stored procedures
(which btw use 1 connection object) gets a different SPID. Why is that?
Shouldn't they use the same SPID, if they use the same connection object
(executed sequentially!)?
Also, the trace shows that each of them gets executed almost instantly
(1ms), but between end of previous, and beginning of the next one, there is
delay of 2-3 seconds. During these 2-3 seconds, there is nothing going on on
the database server (very few events).
Does anybody have an idea?
ThanksPedja wrote:
> Hi,
> We have asp application (ado connection) which connects to the sql
> server 2000 (sp3). There is one asp page which sequentially executes
> set of 6 stored procedures. When I execute same set of stored
> procedures in query analyzer, I get response in less than 1
> second.When application does that, response gets back in 15-20
> seconds (only when we have problem, otherwise in 2-3 seconds). In the
> trace, I noticed that each of these 6 stored procedures (which btw
> use 1 connection object) gets a different SPID. Why is that?
> Shouldn't they use the same SPID, if they use the same connection
> object (executed sequentially!)? Also, the trace shows that each of them
> gets executed almost instantly
> (1ms), but between end of previous, and beginning of the next one,
> there is delay of 2-3 seconds. During these 2-3 seconds, there is
> nothing going on on the database server (very few events).
> Does anybody have an idea?
> Thanks
Are you sure you are not closing the connection and opening it up each time
your ASP code executed some SQL?
David Gugick
Quest Softwaresql