Command time out exception

Question

hi All,

i'm experiencing a problem using SQLServer native client provider in a VB net application.

The query run the first time without problem, retrieving no records.

The second time (after changing parameter values using GUI interface) the query goes in time out exception.

The query is the following:

SELECT SRVQUERY.SESSIONGUID , SRVQUERY.OBJTYPE , SRVQUERY.ID , SRVQUERY.STATUS , SRVQUERY.CAPTION1 , SRVQUERY.CAPTION2 , SRVQUERY.CAPTION3 , SRVQUERY.CAPTION4 , SRVQUERY.CAPTION5 , SRVQUERY.CAPTION6 , SRVQUERY.CAPTION7 , SRVQUERY.CAPTION8 , SRVQUERY.CAPTION9 , SRVQUERY.CAPTION10 , SRVQUERY.CAPTION11 , SRVQUERY.CAPTION12 , SRVQUERY.CAPTION13 , SRVQUERY.CAPTION14 , SRVQUERY.CAPTION15 , SRVQUERY.CAPTION16 , SRVQUERY.CAPTION17 , SRVQUERY.CAPTION18 , SRVQUERY.CAPTION19 , SRVQUERY.CAPTION20 FROM ( SELECT INNERQUERY.ID ID , INNERQUERY.OBJTYPE OBJTYPE , INNERQUERY.SESSIONGUID SESSIONGUID , INNERQUERY.STATUS STATUS , INNERQUERY.CAPTION1 CAPTION1 , INNERQUERY.CAPTION2 CAPTION2 , INNERQUERY.CAPTION3 CAPTION3 , INNERQUERY.CAPTION4 CAPTION4 , INNERQUERY.CAPTION5 CAPTION5 , INNERQUERY.CAPTION6 CAPTION6 , INNERQUERY.CAPTION7 CAPTION7 , INNERQUERY.CAPTION8 CAPTION8 , INNERQUERY.CAPTION9 CAPTION9 , INNERQUERY.CAPTION10 CAPTION10 , INNERQUERY.CAPTION11 CAPTION11 , INNERQUERY.CAPTION12 CAPTION12 , INNERQUERY.CAPTION13 CAPTION13

 , INNERQUERY.CAPTION14 CAPTION14 , INNERQUERY.CAPTION15 CAPTION15 , INNERQUERY.CAPTION16 CAPTION16 , INNERQUERY.CAPTION17 CAPTION17 , INNERQUERY.CAPTION18 CAPTION18 , INNERQUERY.CAPTION19 CAPTION19 , INNERQUERY.CAPTION20 CAPTION20 FROM ( SELECT DISTINCT AOEDF.DATAFILEID ID , 5 OBJTYPE , '58245a7f-8c9f-4728-b003-6ed86a41a518' SESSIONGUID , AOEDF.STATUS STATUS , AODEF.GUID CAPTION1 , AO.GUID CAPTION2 , AOEDF.DATAFILE CAPTION3 , AOEDF.ITEMS CAPTION4 , AOEDF.RUNDATE CAPTION5 , AEP.FILENAME CAPTION6 , SRC_S.GUID CAPTION7 , DST_S.GUID CAPTION8 , SRC_S.SITENAME CAPTION9 , DST_S.SITENAME CAPTION10 , AO.OBJECTTYPEID CAPTION11 , AOEDF.PROCESSED CAPTION12 , '' CAPTION13 , '' CAPTION14 , '' CAPTION15 , '' CAPTION16 , '' CAPTION17 , '' CAPTION18 , '' CAPTION19 , '' CAPTION20 FROM ( ( ( ( ( ( ( ( ( ( ( ( APPOBJECTEXPORTDATAFILE AOEDF JOIN APPOBJECTDEF AODEF ON AOEDF.APPOBJDEFID = AODEF.APPOBJDEFID ) JOIN APPLICATIONSITE SRC_AS ON SRC_AS.APPSITEID = AOEDF.SOURCESITE ) JOIN APPLICATIONSITE DST_AS ON DST_AS.APPSITEID = AOEDF.
DESTINATIONSITE ) JOIN SITE SRC_S ON SRC_S.SITEID = SRC_AS.SITEID ) JOIN SITE DST_S ON DST_S.SITEID = DST_AS.SITEID ) JOIN APPOBJECT AO ON AO.OBJECTID = AODEF.OBJECTID ) JOIN APPLICATION APP ON APP.APPLICATIONID = AO.APPLICATIONID ) LEFT OUTER JOIN APPPACKAGEEXPORTDATAFILE APEDF ON APEDF.DATAFILEID = AOEDF.DATAFILEID ) LEFT OUTER JOIN APPEXPORTPACKAGE AEP ON AEP.PACKAGEID = APEDF.PACKAGEID ) LEFT OUTER JOIN APPEXPORTFILEPACKAGE AEFP ON AEP.PACKAGEID = AEFP.PACKAGEID ) LEFT OUTER JOIN APPEXPORTFILEPACKAGEMESSAGE AEFPM ON AEFPM.FILEPKGID = AEFP.FILEPKGID ) LEFT OUTER JOIN APPSENDMESSAGE ASM ON ASM.MSGID = AEFPM.MSGID ) WHERE ( AOEDF.DELETED IS NULL ) AND ( AODEF.DELETED IS NULL ) AND ( SRC_AS.DELETED IS NULL ) AND ( DST_AS.DELETED IS NULL ) AND ( AO.DELETED IS NULL ) AND ( APP.DELETED IS NULL ) AND ( AEFP.DELETED IS NULL ) AND ( AEP.DELETED IS NULL ) AND ( AEFPM.DELETED IS NULL ) AND ( ASM.DELETED IS NULL ) AND ( 5 = 5 AND ( ( @PROCESSED5FROM IN ( 1 , 3 ) AND AOEDF.PROCESSED IS NOT NULL AND AOEDF.PROCESSED >= @FROMDATE5 ) OR ( @PROCESSED5FROM0 IN ( 2 , 3 ) AND AOEDF.PROCESSED IS NULL AND AOEDF.CREATED IS NOT NULL AND AOEDF.CREATED >= @FROMDATE50 ) ) ) AND ( ( SRC_S.SITEID = @SITEID AND @SITESOURCE = 1 ) OR ( DST_S.SITEID = @SITEID0 AND @SITETARGET = 1 ) ) ) INNERQUERY WHERE ( ID IS NOT NULL ) ) SRVQUERY

The very strange thing is that if i call, for first, the query with parameter values of the "second time call" it retrieve records without problem

It seems to me that if the first call does not retrieve records the second time call generate a query time out exception.

Please help me to find out the problem.

Regards

Luigi

Answer 1

I recommend you to test with

- Check the error message exactly

- Run the query with SQL Server Management Studio after changing the parameters to literal (constant)

- Check the Execution Plan of the query and Duration weathere it runs over 30 seconds or not.

I hope that helps

---

Best Regards, Jungsun Kim

Answer 2

Hi Jungsun,

Thanks for reply

What we did is the following:

- Activated the SQL Profiler tool 

- Run the query 

Here below the result in SQL Profiler grid view:

exec sp_executesql N'SELECT SRVQUERY.SESSIONGUID , SRVQUERY.OBJTYPE , SRVQUERY.ID , SRVQUERY.STATUS , SRVQUERY.CAPTION1 , SRVQUERY.CAPTION2 , SRVQUERY.CAPTION3 , SRVQUERY.CAPTION4 , SRVQUERY.CAPTION5 , SRVQUERY.CAPTION6 , 
SRVQUERY.CAPTION7 , SRVQUERY.CAPTION8 , SRVQUERY.CAPTION9 , SRVQUERY.CAPTION10 , SRVQUERY.CAPTION11 , SRVQUERY.CAPTION12 , SRVQUERY.CAPTION13 , SRVQUERY.CAPTION14 , SRVQUERY.CAPTION15 , SRVQUERY.CAPTION16 , SRVQUERY.CAPTION17 , 
SRVQUERY.CAPTION18 , SRVQUERY.CAPTION19 , SRVQUERY.CAPTION20 FROM ( SELECT INNERQUERY.ID ID , INNERQUERY.OBJTYPE OBJTYPE , INNERQUERY.SESSIONGUID SESSIONGUID , INNERQUERY.STATUS STATUS , INNERQUERY.CAPTION1 CAPTION1 , 
INNERQUERY.CAPTION2 CAPTION2 , INNERQUERY.CAPTION3 CAPTION3 , INNERQUERY.CAPTION4 CAPTION4 , INNERQUERY.CAPTION5 CAPTION5 , INNERQUERY.CAPTION6 CAPTION6 , INNERQUERY.CAPTION7 CAPTION7 , INNERQUERY.CAPTION8 CAPTION8 , 
INNERQUERY.CAPTION9 CAPTION9 , INNERQUERY.CAPTION10 CAPTION10 , INNERQUERY.CAPTION11 CAPTION11 , INNERQUERY.CAPTION12 CAPTION12 , INNERQUERY.CAPTION13 CAPTION13 , INNERQUERY.CAPTION14 CAPTION14 , INNERQUERY.CAPTION15 CAPTION15 , 
INNERQUERY.CAPTION16 CAPTION16 , INNERQUERY.CAPTION17 CAPTION17 , INNERQUERY.CAPTION18 CAPTION18 , INNERQUERY.CAPTION19 CAPTION19 , INNERQUERY.CAPTION20 CAPTION20 FROM ( SELECT DISTINCT AOEDF.DATAFILEID ID , 5 OBJTYPE , 
''fdf0d57a-b26e-4cd1-ae22-3da7feccfe97'' SESSIONGUID , AOEDF.STATUS STATUS , AODEF.GUID CAPTION1 , AO.GUID CAPTION2 , AOEDF.DATAFILE CAPTION3 , AOEDF.ITEMS CAPTION4 , AOEDF.RUNDATE CAPTION5 , AEP.FILENAME CAPTION6 , SRC_S.GUID 
CAPTION7 , DST_S.GUID CAPTION8 , SRC_S.SITENAME CAPTION9 , DST_S.SITENAME CAPTION10 , AO.OBJECTTYPEID CAPTION11 , AOEDF.PROCESSED CAPTION12 , '''' CAPTION13 , '''' CAPTION14 , '''' CAPTION15 , '''' CAPTION16 , '''' CAPTION17 , '''' 
CAPTION18 , '''' CAPTION19 , '''' CAPTION20 FROM ( ( ( ( ( ( ( ( ( ( ( ( APPOBJECTEXPORTDATAFILE AOEDF JOIN APPOBJECTDEF AODEF ON AOEDF.APPOBJDEFID = AODEF.APPOBJDEFID ) JOIN APPLICATIONSITE SRC_AS ON SRC_AS.APPSITEID = AOEDF.SOURCESITE ) JOIN APPLICATIONSITE DST_AS ON DST_AS.APPSITEID = AOEDF.DESTINATIONSITE ) JOIN SITE SRC_S ON SRC_S.SITEID = SRC_AS.SITEID ) JOIN SITE DST_S ON DST_S.SITEID = DST_AS.SITEID ) JOIN APPOBJECT AO ON AO.OBJECTID = AODEF.OBJECTID ) JOIN APPLICATION APP ON APP.APPLICATIONID = AO.APPLICATIONID ) LEFT OUTER JOIN APPPACKAGEEXPORTDATAFILE APEDF ON APEDF.DATAFILEID = AOEDF.DATAFILEID ) LEFT OUTER JOIN APPEXPORTPACKAGE AEP ON AEP.PACKAGEID = APEDF.PACKAGEID ) LEFT OUTER JOIN APPEXPORTFILEPACKAGE AEFP ON AEP.PACKAGEID = AEFP.PACKAGEID ) LEFT OUTER JOIN APPEXPORTFILEPACKAGEMESSAGE AEFPM ON AEFPM.FILEPKGID = AEFP.FILEPKGID ) LEFT OUTER JOIN APPSENDMESSAGE ASM ON ASM.MSGID = AEFPM.MSGID ) WHERE ( AOEDF.DELETED IS NULL ) AND ( AODEF.DELETED IS NULL ) AND ( SRC_AS.DELETED IS NULL ) AND ( DST_AS.DELETED IS NULL ) AND ( AO.DELETED IS NULL ) AND ( APP.DELETED IS NULL ) AND ( AEFP.DELETED IS NULL ) AND ( AEP.DELETED IS NULL ) AND ( AEFPM.DELETED IS NULL ) AND ( ASM.DELETED IS NULL ) AND ( 5 = 5 AND ( ( @PROCESSED5FROM IN ( 1 , 3 ) AND AOEDF.PROCESSED IS NOT NULL AND AOEDF.PROCESSED >= @FROMDATE5 ) OR ( @PROCESSED5FROM0 IN ( 2 , 3 ) AND AOEDF.PROCESSED IS NULL AND AOEDF.CREATED IS NOT NULL AND AOEDF.CREATED >= @FROMDATE50 ) ) ) AND ( ( SRC_S.SITEID = @SITEID AND @SITESOURCE = 1 ) OR ( DST_S.SITEID = @SITEID0 AND @SITETARGET = 1 ) ) ) INNERQUERY WHERE ( ID IS NOT NULL ) ) SRVQUERY',N'@PROCESSED5FROM decimal(38,0),@FROMDATE5 datetime,@PROCESSED5FROM0 decimal(38,0),@FROMDATE50 datetime,@SITEID decimal(38,0),@SITESOURCE decimal(38,0),@SITEID0 decimal(38,0),@SITETARGET decimal(38,0)',@PROCESSED5FROM=2,@FROMDATE5='2012-06-03 13:46:34.600',@PROCESSED5FROM0=2,@FROMDATE50='2012-06-03 13:46:34.600',@SITEID=23000000002,@SITESOURCE=1,@SITEID0=23000000002,@SITETARGET=0

If i run the above statement using SQL Management console i don't have any trouble (of course i've substitute values with the ones giving me time out exception)

Here below the exception i receive:

The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

Any other suggestions?

Regards

Luigi

Answer 3

Looks like the application is taking more than the default 60 seconds to send the request to SQL server and receive a response.

Probably silly of me, but did you try setting the command timeout property in VB.NET code to 300 (5 minutes) / 600 (10 minutes) and check.

---

NSK