Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO Timeout expired

Posted on 2006-11-14
14
Medium Priority
?
2,065 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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

715 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