Timeout when passing large table-valued parameter

3

I am calling a SQL Server stored procedure using ADO.NET, and I have to pass a very large Table-Valued parameter. I get this timeout exception very often:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding. --->
System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)

I am calling ExecuteNonQueryAsync, which according to the documentation is not affected by the CommandTimeout.

The question is: what timeout value should I increase to be able to pass that large TVP to the stored procedure without getting a timeout?

c#
sql-server
ado.net
timeout
table-valued-parameters
asked on Stack Overflow May 30, 2014 by Flavien

1 Answer

0

A solution that helped me recently with this situation was increasing the Connection Timeout (not to be mistaken with Command Timeout). This makes me think that sometimes when there is a lot of data being passed to a stored procedure via a table value parameter that it can sometimes take longer to initialize the connection (this occurred during peak usage but not when the system was under low load). A simple way to increase the Connection Timeout is via the query string.

Example of setting timeout to 120 seconds instead of the default 15:

"Data Source=localhost;Integrated Security=SSPI;Connection Timeout=120;"
answered on Stack Overflow Jan 24, 2019 by N0Alias

User contributions licensed under CC BY-SA 3.0