I have a stored procedure which uses a table valued parameter (tvp). In my application, I use a datatable as a SqlParameter to match the tvp structure. The problem is that it sometimes takes 25 seconds just to insert the data (30k rows give or take) into the tvp from the application once I've already executed the stored procedure, meaning the code inside the stored procedure itself only has 5 seconds (with a command timeout of 30 seconds) to complete which doesn't always happen with large amounts of data.
I'm fully aware that I can just increase the command timeout, but I'd like to get to the bottom of why it takes 25 seconds to insert the data into the tvp and what can be done to speed this up.
Just to be clear, this isn't the code inside the stored procedure in SSMS which is taking the 25 seconds, it's the application itself inserting the rows into the tvp after I've executed the stored procedure from the application.
This offending statement is below (our tvp has roughly 20 columns):
declare @p3 dbo.table_valued_parameter insert into @p3 (col1, col2, col3) values (v1, v2, v3)
My question is, why does it take 25 seconds to insert the 30k rows into the tvp and what methods can I use to speed this up? Perhaps the issue is using a DataTable for the SqlParameter? I also would have thought CommandTimeout would only start counting once the stored procedure itself has begun executing in SSMS, rather than beginning the count while preparing the parameters.
C# code below as requested (The GetDataTable method creates a DataTable by adding columns to a new DataTable which matches the definition of the tvp, and then adds rows to the DataTable by iterating over a list used elsewhere in the code).
List<SqlParameter> parameters = new List<SqlParameter>()
{
new SqlParameter("@textParam1", "Value1"),
new SqlParameter("@testParam2", "Value2"),
new SqlParameter("@tvp", GetDataTable())
};
DataSet dataSet = new DataSet();
SqlCommand command = new SqlCommand(StoredProcName);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters.ToArray());
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
command.Connection = connection;
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(dataSet);
}
connection.Close();
}
