Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ADO Timeout expired

Posted on 2006-11-14
14
Medium Priority
?
2,068 Views
Last Modified: 2013-12-25
I'm getting a ADO Time out error, when i run this query from VB using ADO but the same time takes about 13seconds to run in Query analyzer. Remember that i did set the cmd timeout to Zero. Check my code below. I don't think i'm doing anything wrong here, infact this is how i do it every time just works fine but it doesn't work for single client.

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
conn.ConnectionTimeout = 0
cmd.CommandTimeout = 0

GetJobRS:

15600   strSQL = "Select A.* From CAMS_rptJobAvailable_View A " + _
            "  where A.PositionsAvailable > A.PositionsFilled" + _
            "  and A.JobStatus <> 'Cancelled'" + _
            "  and exists(select top 1 CumGPA from CAMS_Placement_View B " + _
            "  Inner Join " + strTempTable + " as C on C.StudentUID = B.StudentUID" + _
            "  where B.CumGPA >= A.minimumGPA )"

15620       Set mdbQueryAuditObj = GetObjectContext.CreateInstance("CAMSGeneral.busCAMSGeneral")
15630       Result = mdbQueryAuditObj.WriteQueryAudit(strUserName, strDBName, strSvrName, strSQL, strError)
15640       Result = 0
15650       Set mdbQueryAuditObj = Nothing
15660       JobRs.ActiveConnection = conn
15670       JobRs.CursorLocation = adUseClient
15680       JobRs.CursorType = adOpenStatic
15690       JobRs.LockType = adLockOptimistic
15700       JobRs.Open strSQL
                                          ('JobRs.Open strSQL, Conn) - I used this statement as well as above statement but same result.
15710       JobRs.ActiveConnection = Nothing
15720       Set rsJobs = JobRs

I get timeout error on line 15700. Same exact query works locally and also on our server . Let me know if you guys have any idea.
0
Comment
Question by:onebite2
  • 3
  • 2
  • 2
  • +4
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17944832
13 seconds is already alot.
the difference (ie the timeout) can already happen if you use another connection method than query analyser, what is your connection string like?
how many records are being retrieved? if it's only few records, the query should be tuned by adding another index for example...
0
 
LVL 5

Accepted Solution

by:
Venkateswarlu Kaipu earned 2000 total points
ID: 17945927
Hi

Use cnn.CommandTimeout = 300
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:onebite2
ID: 17947200
AngellIII,Only 10 records are going to be retrieved.
Venkat, I'll do that but i'm just curious what's wrong with cmd.CommandTimeout = 0 also you statement has cnn.commandTimeout=300 (not command object your using connection object do you thinks its okay).
0
 

Author Comment

by:onebite2
ID: 17947632
I used conn.commandtimeout=300,It does work fine now.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17948506
can you post some information about the explain plan you can generate with the query analyser?
possibly do a screenshot of it, and upload it to www.ee-stuff.com 
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17948740
Ok, lets try something simple:

strSQL = "Select A.* From CAMS_rptJobAvailable_View A " + _
            "  where A.PositionsAvailable > A.PositionsFilled" + _
            "  and A.JobStatus <> 'Cancelled'" + _
            "  and exists(select top 1 CumGPA from CAMS_Placement_View B " + _
            "  Inner Join " + strTempTable + " as C on C.StudentUID = B.StudentUID" + _
            "  where B.CumGPA >= A.minimumGPA )"

rsJobs.Open strSQL, conn, adOpenStatic, adLockOptimistic

Is this faster?

Leon
0
 
LVL 8

Expert Comment

by:doobdave
ID: 17979660
so perhaps it was you connection that was timing out, and not the query.
Which means you have a bad connection to the database.

As AngelIII suggests, 13 seconds is rather a long time for a query to return.
Check the query execution plan to see if indexes are being utilized
0
 
LVL 4

Expert Comment

by:Clothahump
ID: 17983099
To expand on doobdave's answer, very specifically check for indexes used on
the JobStatus field in CAMS_rptJobAvailable_View and both of the StudentUID fields, especially the one in your temp table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18034657
>>I'll do that but i'm just curious what's wrong with cmd.CommandTimeout = 0 <<
You are not using the cmd object to execute your query.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20825822
objection:

comment http://www.experts-exchange.com/Q_22061026.html#a17947632
I used conn.commandtimeout=300,It does work fine now.

which has been suggested by venkat_dh just above.

I see no reason why the question should be deleted
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20828454
I agree.  Points should be awarded to venkat_dh
0
 
LVL 4

Expert Comment

by:Clothahump
ID: 20829461
I agree.  Venkat_dh should get the points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

972 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