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

x
?
Solved

ADO Timeout

Posted on 2003-11-17
12
Medium Priority
?
1,673 Views
Last Modified: 2012-08-13
A have a stored procedure that executed within 0.5 sec from Query Analyzer.
The same procedure with the same parameters, if called remotely using ADO, returns the data within the time defined as ADO timeout + 0.5 sec.
In the Profiler, I can see that nothing happened between the SP end and RPC SP end; it just waits as long as ADO timeout defined for this call.
It happens eventually, not always.

Thank you!

0
Comment
Question by:zachar
[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
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9764524
Try posting the relevant code.  Perhaps someone can see something.

Anthony
0
 
LVL 1

Author Comment

by:zachar
ID: 9769306
Here is ADO call. I can provide SP code as well, but it's pretty long...

''----------------------------------------------------------------------------------------------
Dim objADOCommand As New ADODB.Command
Dim objADODSConnection As ADODB.Connection
Dim objADORecordset As New ADODB.Recordset



Set objADODSConnection = New ADODB.Connection

objADODSConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBVIHosp;Data Source=DVDEVSQL2"
objADODSConnection.CursorLocation = adUseClient
objADODSConnection.Mode = adModeUnknown
objADODSConnection.Open

Set objADOCommand = New ADODB.Command
Set objADOCommand.ActiveConnection = objADODSConnection
objADOCommand.CommandType = adCmdText
objADOCommand.CommandText = "execute PatientAspects_cmd_prc '303056217', 1, '1,2,3,5,6,7,8,20,9,13,10,14,16,15,17,18', '0,0,0,0,0,0,0,0,0,0,0,1!2!3!4!5!6,0,0,0,0', '128', 1, '128', 1, '1963-11-17,1963-11-17,1963-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,1983-11-17,2002-11-17', '2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01,2079-01-01'"
objADOCommand.CommandTimeout = 5

   Set objADORecordset = objADOCommand.Execute

objADORecordset.Close
objADODSConnection.Close

''----------------------------------------------------------------------------------------------
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9771645
I do not see anything obvious that could be causing prroblems.  One minor (and unrelated) problem: There is no need to instantiate the Command and Recordset object twice.  Instead of:

Dim objADOCommand As New ADODB.Command
Dim objADODSConnection As ADODB.Connection
Dim objADORecordset As New ADODB.Recordset

Change it to:
Dim objADOCommand As ADODB.Command
Dim objADODSConnection As ADODB.Connection
Dim objADORecordset As ADODB.Recordset

I do not believe this will make any difference, but have you tried setting up the Command object's Execute method  explicitly defining the parameters?

One thing that you should be aware of (in case you are not) is that there are a couple of differences between your ADO code and Query Analyzer
1. QA has an infinite timeout.
2. QA uses the ODBC provider and you are using the OLEDB provider.

Again, I realize this is unrelated, but have you considered streamlining your Stored Procedure? You are passing 10 parameters, many of which appear to include comma delimited parameters, which I am assuming you are parsing out into temporary tables.

Sorry I did not have any direct help fro your problem,

Anthony
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 1

Author Comment

by:zachar
ID: 9776771
Thanks for help!
I have tried all this - no result… As well I have connected with ODBC data source, with the same result.
Again, this phenomenon happens sometimes, and restarting SQL server removes it, or it disappears by itself.
I saw such behavior once, and than simple recompiling of SP removed the problem completely, but it does not help now.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9776789
I am sorry, but I am out of ideas.  Hopefully someone can help you out.

Anthony
0
 
LVL 9

Expert Comment

by:miron
ID: 9807208
most probably you need to tune indexes used within the procedure query. When you execute query via SQL Query Analyzer on the local machine you often do not compete for the same resources as the queries executed from ADO. Translates in the totally different timing.

--cheers
0
 
LVL 1

Accepted Solution

by:
zachar earned 0 total points
ID: 9808782
I do not think so. I saw in query analizer, when the procedure is called remotely, the SP is finished within the normal time, and then between SP finish and RPC finish there is no activity. As well, the execution plans are the same whether SP called from QA or by RPC.

Anyway, I have found something suspicious- one of the clients was connected using Named Pipes, all others - TCP. Since this client fixed protocol to TCP, this strange behavior not returned. Of cause I am not sure that is the reason of the problem, I will just wait to be sure.

Thank you.
0
 
LVL 9

Expert Comment

by:miron
ID: 9808821
I think you found it. Named pipes protocol is slow to close connections and have caused effect similar to wheat you have indicated.
0
 
LVL 9

Expert Comment

by:miron
ID: 9809083
I suggest Zachar be refunded the points and finalize this question by keeping it for EE ( not sure guys how you go about it ) since it has a useful information.
Hope Zachar would agree with this suggestion. If I do not hear by the end of day will post a note into the appropriate EE board.

Thank you.
0
 
LVL 1

Author Comment

by:zachar
ID: 9809098
I agree.

Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9811133
Here is how to do it:

I answered my question myself. What do I do?
http://www.experts-exchange.com/help/closing.jsp#5

Anthony
0
 

Expert Comment

by:YensidMod
ID: 9812197
Question is PAQed and 500 points refunded.

YensidMod
Experts Exchange Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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