zachar
asked on
ADO Timeout
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!
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!
ASKER
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.Connect ionString = "Provider=SQLOLEDB.1;Integ rated Security=SSPI;Persist Security Info=False;Initial Catalog=DBVIHosp;Data Source=DVDEVSQL2"
objADODSConnection.CursorL ocation = adUseClient
objADODSConnection.Mode = adModeUnknown
objADODSConnection.Open
Set objADOCommand = New ADODB.Command
Set objADOCommand.ActiveConnec tion = 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,1 7,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,196 3-11-17,19 83-11-17,1 983-11-17, 1983-11-17 ,1983-11-1 7,1983-11- 17,1983-11 -17,1983-1 1-17,1983- 11-17,1983 -11-17,198 3-11-17,19 83-11-17,1 983-11-17, 2002-11-17 ', '2079-01-01,2079-01-01,207 9-01-01,20 79-01-01,2 079-01-01, 2079-01-01 ,2079-01-0 1,2079-01- 01,2079-01 -01,2079-0 1-01,2079- 01-01,2079 -01-01,207 9-01-01,20 79-01-01,2 079-01-01, 2079-01-01 '"
objADOCommand.CommandTimeo ut = 5
Set objADORecordset = objADOCommand.Execute
objADORecordset.Close
objADODSConnection.Close
''------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
''------------------------
Dim objADOCommand As New ADODB.Command
Dim objADODSConnection As ADODB.Connection
Dim objADORecordset As New ADODB.Recordset
Set objADODSConnection = New ADODB.Connection
objADODSConnection.Connect
objADODSConnection.CursorL
objADODSConnection.Mode = adModeUnknown
objADODSConnection.Open
Set objADOCommand = New ADODB.Command
Set objADOCommand.ActiveConnec
objADOCommand.CommandType = adCmdText
objADOCommand.CommandText = "execute PatientAspects_cmd_prc '303056217', 1, '1,2,3,5,6,7,8,20,9,13,10,
objADOCommand.CommandTimeo
Set objADORecordset = objADOCommand.Execute
objADORecordset.Close
objADODSConnection.Close
''------------------------
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
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
ASKER
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.
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.
I am sorry, but I am out of ideas. Hopefully someone can help you out.
Anthony
Anthony
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
--cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you found it. Named pipes protocol is slow to close connections and have caused effect similar to wheat you have indicated.
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.
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.
ASKER
I agree.
Thank you.
Thank you.
Here is how to do it:
I answered my question myself. What do I do?
https://www.experts-exchange.com/help/closing.jsp#5
Anthony
I answered my question myself. What do I do?
https://www.experts-exchange.com/help/closing.jsp#5
Anthony
Question is PAQed and 500 points refunded.
YensidMod
Experts Exchange Moderator
YensidMod
Experts Exchange Moderator
Anthony