sql timeout from web application problem (works good in sql server

[dave]

I have a query that returns in less than 2 seconds when i run from sql
enterprise manager (sql2005) However..when a user enters criteria and hits
'search' which executes the identical query the query time's out.
In a test environment I profiled sql server and captured the identical
query. I can execute that just fine through query analyzer.
I have set the command and connection timeout to 5 minutes (way more than
required) however i still get an error like the following...

SQL Exception Information:
ErrorId: -2
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.
Line Number: 0
Procedure:


So the question is how is running through the web application different
against a sql server database. The only difference that i can think of is
that there may be different protocol that may be used between these two
different interfaces to sql server.

Any ideas would be great appreciated. I have been chasing this sort of
thing for many months.
thx -dave

 

[sloan]

Google
"Parameter Sniffing" "Sql Server"

 

[daveh551]

Google
"Parameter Sniffing" "Sql Server"

Just a guess, but if it's not returning in some reasonable time, I
would look at not the query but at whether or not it's actually
connecting to the server at all. Are other queries running okay? If
so, then maybe this one doesn't have the connection set the way you
think it does. Or possibly, running unders IIS, it's connecting to
the server as a different user than you think, or that it doesn't have
the right access permissions. I'm not sure how you would go about
diagnosing it, but that's where I would look.