?
Solved

SQL Timeout error

Posted on 2011-09-14
7
Medium Priority
?
575 Views
Last Modified: 2012-05-12
I have a query that takes about 35 seconds to return. The same query returning in 15-20 seconds works fine. The problem occurs both inside my website and through my remoterly connected Visual Studio.
I have already changed the 'Connection Timeout' value to no effect [I'm aware that the default is 15 seconds - I changed it to 100, to 200 and 0 [in case that was a 'wait forever' flag !]
I CAN extract the data directly by going onto to my server and running the query from within the SQL Studio on that server and also remotely using my SQL Studio from here.
The issue doesn't appear to be the connection string

what else??
D.

0
Comment
Question by:CTOSian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36540030
You indicate this is a web application, have you checked the executionTimeout setting within Web.config or the session timeout of the application within IIS? I would suspect the former more so than the latter since this is happening on your VS also.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 36540907
Hi CTOSian,

You should change CommandTimeout, not ConnectionTimeout, like this:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT ...";
cmd.CommandTimeout = 60;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36541053
Good point. That is likely the culprit. Try that. You just have to ensure that your executionTimeout is set higher than whatever you end up needing for CommandTimeout.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:CTOSian
ID: 36541595
To be clear, I get this problem when running the Stored Procedure against the database in the VisualStrudio Server Explorer.
I see no opportunity to change the command Timeout either in the properties of the Connection string or the properties of the Stored Procedure....

In my code, I am using the Microsoft SqlHelper.ExecuteReader method to read data from the database and again, I see no opportunity to set the command timeout.

I have the following code :
Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SPName", Param1, Param2), IDataReader)

Which I suspect I may need to change to include a command timeout value...
... Some hgelp here would be appreciated.

but this doesn't apply to the VisualStudio part where I run the SP directly...
help needed here too

D.

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36541668
This might sound crazy, but check out: http://msdn.microsoft.com/en-us/library/ms190181.aspx
puzsol posted a root cause of 'SQL 2008 SP1' which was confirmed and posted to connect.

The workaround is illustrated here -- http://cushen.wordpress.com/2011/04/06/sql-server-2008-timeout-expired-error-and-sp1/ (turn off "Allow Remote server connections" then turn back on. I know. Try it, though. Apparently it works. I haven't noticed this issue myself, but I have SSMS installed locally so don't tend to query SQL from VS.
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 36541677
1) Visual Studio Server Explorer

Go to Tools->Options->Database Tools->Query and View Designers
If you do not want to have timeout at all, uncheck "Cancel long running query", otherwise set appropriate value at "Cancel after:"


2) SqlHelper.ExecuteReader

I'm afraid you will have to modify SqlHelper class or use another way to access your data


3) Are you sure you procedure is optimized enough? Maybe you should make it run faster (if possible) instead of using longer timeout?
0
 

Author Closing Comment

by:CTOSian
ID: 36541979
This answers the Visual Studio issue - Thanks.
I'm going re-issue the coding problem [after more research] as I think that I need to issue a more discrete reader command [one that allows me to add the commandtimeout attribute - SQLHelper.ExecuteReader doesn't do that....]

Thanks!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question