SQL Timeout through ADO but not from Query Analyser

SQL Timeout through ADO but not from Query Analyser

• bill_csharper

  More on this problem. I'm experiencing it now on another server and a different query (still using FOR XML EXPLICIT). It's not consistent, I can call the proc several times in a row and it will time out a few times in a row, then for no apparent reason it will return in less than a second.
  
  

• oatman

  Hi SilentC,

  This might occur if you xml value has large amount of data. The SQLOLEDB/SQLNCLI OLEDB providers by default cache data values into internal buffers in order to allow random access to the user. This is the default behaviour. You can try changing the command property: "Access Order" to Sequential (0) and see if that helps. I have sample code here that sets the property:

  Sub MSDNSeqOrder()
  Dim cn As New ADODB.Connection
  On Error GoTo ErrHandler
  cn.ConnectionString = "Provider=SQLOLEDB;Server=<your server name>;Initial Catalog=pubs;Integrated Security=SSPI;"
  cn.Open
  Dim cmd As New ADODB.Command
  Dim rs As New ADODB.Recordset
  Set cmd.ActiveConnection = cn
  cmd.CommandText = "select * from pubs..authors for xml auto"
  cmd.Properties("Access Order") = 0
  Set rs = cmd.Execute
  MsgBox "RS1=" & rs.Fields(0).Value
  rs.Close
  Exit Sub
  ErrHandler:
  MsgBox Errors.Count
  End Sub

  Thanks

  Waseem

  
  
  

• Tryst

  I have a query that will timeout in 30 seconds from SQLOLEDB client but runs fine (40 seconds) in QA. After reading through all the matter, I moved it to a stored procedure and for at least now, it seems to work.

  Malay

  
  

• Jack Spade

  Perfect LuckyW This seems like the exact same propblem that i've been experiancing with both our ADO and ADO.NET systems. It was apparent that there was some difference between the way that QA and ADO ran SPs but until now I was unsure what it was, thankyou for sharing your research, it was a great help.

  
  

• Biju S Melayil

  Yes, the query uses FOR XML EXPLICIT
  
  

• Bandile

  I had the same problem.  Seems like the SQL execution plan was corrupted for the stored procedure.  I created a new SP and and copied the code to the new SP from the old and ran the application and the data was returned in no time.  This proves that this was a SQL server issue and probably the execution plan for the SP was not efficient when it is run from ADO.NET.  The reason that the SP worked from the query analyser and not the application using ADO.NET is QA directly executes the SP on the server but ADO.NET internally executes the sp by calling sp_executesql.  The dropping and recreating the SP from the database should be a quick fix for the future.  It may be worth while to find out WHY this condition happens after a while on the database.

  Here are some information from the research I did on this for your reference:
  1) “SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure.” (http://msdn2.microsoft.com/en-us/library/aa720629(VS.71).aspx)
  2) “Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled.”
  (http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
  3) “You may have heard about a system stored procedure called sp_executesql. It lets you evaluate dynamic SQL, but it happens to also cache its execution plan.”
  (http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx)
  4) “The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used. ”
  (http://www.databasejournal.com/features/mssql/article.php/1565961)
  

  
  

• Craig Mackles

  I came in this morning to work on this problem but it is no longer timing out. In fact it is returning now in less than a second as it was last week in Query Analyser. The only thing I know that has happened over the weekend is that a scheduled back up of the DB and transaction log has taken place. Do you think the problem could have been related to a large transaction log perhaps
  
  EDIT: The amount of data being returned is comparatively small, I have other queries which return much more data and which have not been subject to this time out issue.
  
  

• Aleksandr Tokarev

  Could the problem be caused because of locks on the table/view that you are trying to query from This document might help you in identifying any locking issues: http://msdn2.microsoft.com/en-us/library/aa213025(SQL.80).aspx

  -- Waseem

  
  
  

• omtslug

  For some reason I can't access this forum anymore from my machine. I'm using remote desktop to post from another machine. Anyway, this is my excuse for only replying now.

  In answer to your first question, no I never did find out what was causing it. The problem just stopped happening of it's own accord. If I get it again, I'll certainly look into your solution. Thanks for posting it.

  
  

• sjb31988

  No problem. For SP's that start to behave this way after a while it is better to use the 'WITH RECOMPILE' option. As it is better SQL server recompiles the SP right from the start without using an inefficient query plan
  

• dr.acv

  SilentC,

  Did your query involve "for xml" command

  Thanks

  Waseem

  
  
  

• pjhunter

  Hi,  Were you able to identify the problem and find a solution for it.  I am also encountering a similar behavior with a SP and I have not been able to figure out what's going on.  Any help will be appreciated.

   

  Thanks

  LW

  
  
• SQL Timeout through ADO but not from Query Analyser