Solved

ADO Timeout expired

Posted on 2006-11-14
14
2,005 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
Hi

Use cnn.CommandTimeout = 300
0
 
LVL 5

Expert Comment

by:Venkateswarlu Kaipu
Comment Utility
0
 

Author Comment

by:onebite2
Comment Utility
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
Comment Utility
I used conn.commandtimeout=300,It does work fine now.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
I agree.  Points should be awarded to venkat_dh
0
 
LVL 4

Expert Comment

by:Clothahump
Comment Utility
I agree.  Venkat_dh should get the points.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now