Very slow queries from ASP

Hi

I'm seeing a very weird (and new) phenomena on two of my
IIS servers.

Suddenly, queries are very very slow.
The weird part is that if i run the query from Query
Analyzer it works great (0 seconds) but if i run it from
ASP i get a very slow responce and sometimes even timeout.

any idea what is diffrent between ASP & QA?

I'm using the next connection test page :

<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\ado\msado15.dll"-->
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

sConnectionString = _
"Provider=SQLOLEDB.1;" &_
"Data Source=MySQL;" &_
"Initial Catalog=MyDatabase;" &_
"OLE DB Services=-1;" &_
"Integrated Security=SSPI"

Conn.Open sConnectionString
strQuery = "EXEC DB.dbo.MySP_that_does_something
@UserName='MyUser'"
RS.Open strQuery, Conn
Response.write RS.Fields(0)
Conn.close
set Conn = Nothing
set RS = Nothing
%>

Bob
Mon Dec 29 09:14:35 CST 2003

berber wrote:
> Hi
>
> I'm seeing a very weird (and new) phenomena on two of my
> IIS servers.
>
> Suddenly, queries are very very slow.
> The weird part is that if i run the query from Query
> Analyzer it works great (0 seconds) but if i run it from
> ASP i get a very slow responce and sometimes even timeout.

You may be running into a behavior called "parameter sniffing". Here is an
explanation from Bart Duncan:

http://tinyurl.com/h7aa

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"