Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO Timeout expired

Posted on 2006-11-14
14
Medium Priority
?
2,071 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
13 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Industry Leaders: 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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

581 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