?
Solved

Query timeout expired (ASP) but only 3 times then it it's okay

Posted on 2014-10-09
4
Medium Priority
?
200 Views
Last Modified: 2014-10-22
Our CMS is experiencing 'Query timeout expired' after updating records.

But it only happens 3 times at the start of each day, then it no longer happens until the next day (odd).

CMS using ASP Classic code.

Starting happening after our IS/IT department upgraded to MS SQL Server 2008 R2.

Not sure why it's happening. I think the code stop is not always the same but in my last test it happened after a System.Connection.Execute.
0
Comment
Question by:intoxicated_curveball
[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
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40370520
The first time a query runs takes always more time since there's nothing in the buffer. After that data are most expected to be in the buffer so the engine will get data from memory instead of from disk.
Anyway you should ask how they upgraded SQL Server. Check with them if they rebuild indexes after migration.
You can also post here the query so we can see if there's any improve that can be done.
0
 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40370525
From what version of SQL server did you upgrade?  Depending on the version there may be syntax issues with some queries.  But that depends on from where you came.

What is the app trying to do when it tries to do the execute?

Using SQL Server Management Studio, have you tried to directly execute the query to see if data is being returned?

Dan
0
 

Author Comment

by:intoxicated_curveball
ID: 40370579
Vitor:

The query is

MaxRank = getInt(System.Connection.Execute("SELECT MAX(T.[Rank]) FROM [" & Me.Title & "] AS T")(0))

Open in new window


But I don't think it's always the same every day. I can't confirm until tomorrow since the issue will no longer happen today.

Dan:

Was SQL Server 2005 previously.

I don't see how it can be a syntax issue if there's no syntax error and it only happens three times (timeout) then doesn't happen again that day.

I will try the Query directly tomorrow.

Is there any way to just extend the Query timeout time?
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40370678
You can change the query timeout time in your connection but you should avoid that unless in extremely cases that are really necessary.
What can also happens is maybe somehow in the morning that table have a lot of simultaneous access so what you can do here is to use a NOLOCK locking hint:
MaxRank = getInt(System.Connection.Execute("SELECT MAX(T.[Rank]) FROM [" & Me.Title & "] AS T WITH (NOLOCK)")(0))

Open in new window

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 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