Solved

ADO Timeout expired

Posted on 2006-11-14
14
2,059 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
[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
  • +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 500 total points
ID: 17945927
Hi

Use cnn.CommandTimeout = 300
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month4 days, 23 hours left to enroll

636 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