In code that I inherited, I have a SqlDataSource that has a fairly complex select statement that for certain SelectParameters, always times out ("Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.").
When I run the exact same query with the same parameters in management studio, the query never times out and always takes less than a second.
Does anyone have an idea what the problem could be here? I can't make sense of it.
could be locking/blocking, if people are doing work in the database your select may wait until their transaction is complete. The timeout would be hit or miss, depending on the other transactions in the database.
in management studio, run SET SHOWPLAN_ALL ON, and then run your query. Look for "SCAN" in the output. If you have a table or index scan you are more likely to be a victim of locking/block, since you must process the entire index/table and anyone locking a row in there will force you to wait.
when you run the application, and screen is not refreshing fast run this in management studio:
EXEC sp_lock
it will give you some basic info an any locking currently going on.
Just a shot in the dark: The parameters are not actually the same. In SSMS you pass in ASCII parameters for the query, while in ADO.Net you pass Unicode ones. SqlCommand.Parameters.AddWithValue("@myParam", myValue) will add a parameter of type NVARCHAR when myValue is a String. Due to te conversion rules in SQL if you have SELECT ... FROM ... WHERE myField = @myParam and myField is Ascii (VARCHAR) and @myParam is Unicode (NVARCHAR) then the execution must do a table scan, cannot use an index on myField, resulting in awfull performance when compared to SSMS execution.
As I said, this is just a shot in the dark, but is a common pitfall and fairly subtle to debug.
Try following, maybe this will clarify what's going on:
In sql profiler, capture exact statement into which your complex SQL statement translates and run it in Viual Studio.
When the sql statement in question is running, go check activity monitor in management studio. It could give you an idea, what might be blocking the sql.
It's important to see what else is running at the same time. Is application multi-threaded? Is sql connection getting closed/disposed immediately after use (if not, it may not get closed timely)? Is same sql connection used by multiple threads?