Table valued parameters - time out

Question

I have implemented Table valued parameters that gets populated from C# program and uses stored procedure to finally store in tables. I found that if I send in 75 records at a time, I do not get time out or else I do. How can I insert more records at a time?

-- select TOP 75 master recs where....& each master has several detail recs

-- created List of Collection and added these 75 recs plus their detail recs and called insertComp.

private static void insertComp(List<MasterTbl> newMaintbl,List<DetailTbl> newDetailtbl)
      {
          Int32 rowsAffected = 0;
          try
          {
              DataSet ds = new DataSet();
              DataTable dtMasterVerified = CreateDatatableMaster();
              DataTable dtMasterVerified1 = dtMasterVerified.Clone();
              DataTable dtDetail = CreateDatatableDetail();
              DataTable dtDetail1 = dtDetail.Clone();
              foreach (AddressVerifiedTbl tmp in newMaintbl)
              {
                  DataRow drMaster = dtMasterVerified1.NewRow();
                  drMaster["ID"] = tmp.ID;
                  drMaster["Address1"] = tmp.Address1;
                  drMaster["Address2"] = tmp.Address2;
                  drMaster["city"] = tmp.city;
                  drMaster["state"] = tmp.state;
                  drMaster["zip"] = tmp.zip;
                  drMaster["country"] = tmp.country;
                  drMaster["Status"] = tmp.Status;
                  drMaster["errormsg"] = tmp.errormsg;

                  foreach (DetailTbl tmpDetail in newDetailtbl)
                  {
                      DataRow drDetail = dtDetail1.NewRow();
                      drDetail["ID"] = tmpDetail.ID;
                      drDetail["name"] = tmpDetail.name;
                      drDetail["updDate"] = tmpDetail.updDate;
                      dtDetail1.ImportRow(drDetail);
                      dtDetail1.Rows.Add(drDetail);
                  }
                  dtDetail1.ImportRow(drMaster);
                  dtDetail1.Rows.Add(drMaster);
              }

              string connectionString = GetConnectionString();
              using (SqlConnection connection = new SqlConnection(connectionString))
              {
                  connection.Open();
                  SqlCommand cmd = new SqlCommand("dbo.uspInsert_temp_AllRecs", connection);
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.CommandTimeout = 2000;
                  SqlParameter parameter = new SqlParameter();
                  parameter.ParameterName = "@MasterObj";
                  parameter.SqlDbType = System.Data.SqlDbType.Structured;
                  parameter.Value = dtMasterVerified1;
                  parameter.TypeName = "db.Master_Verified";
                  cmd.Parameters.Add(parameter);

                  SqlParameter parameter2 = new SqlParameter();
                  parameter2.ParameterName = "@DetailObj";
                  parameter2.SqlDbType = System.Data.SqlDbType.Structured;
                  parameter2.Value = dtDetail1;
                  parameter2.TypeName = "db.Detail_verified";
                  cmd.Parameters.Add(parameter2);

                  try
                  {
                      rowsAffected = cmd.ExecuteNonQuery();
                  }
                  catch (Exception ep)
                  {   
                     Console.WriteLine(ep.Message);
                  }
                 }
              }
          catch (Exception e)
          {
             Console.WriteLine(e.StackTrace);
           }
          }


 CREATE PROCEDURE [db].[uspInsert_temp_AllRecs]
@MasterObj Master_Verified Readonly, @DetailObj Detail_verified Readonly
AS
 INSERT INTO db.temp_Master_tbl (....)
SELECT * from @MasterObj
 INSERT INTO db.temp_Detail_tbl (....)
Select * from @DetailObj

Thanks R

Could you provide more details please? 1. What is the typical and max number of rows you are planning to insert with this SP? (If it is a huge number, you might need to consider BULK INSERT. If it is just hundreds or a few thousands - TVP should be fine) 2. What time does it take now to insert 75 records? 3. Have you identified if the SP is your bottleneck, or passing the data to the server, or something else? — helix, Apr 29 '14 at 21:26

score 1 · Answer 1 · edited Jan 23 '15 at 16:35

1

I just had this problem and I can't be sure that we have the exact same problem given the lack of information, but I'll post it here and hope it helps.

I was using my table valued parameter in a join. When I moved it to an exists statement, the timeout went away. I wish I had better reason why other than a hunch about how the query planner works.

Original - Causes Time-out:

select t.* 
from dbo.Table1 as t1 
inner join @table as t2 on t1.val = t2.val

Modified - Works:

select t.* 
from dbo.Table1 as t1 
where exists(select null from @table where t2.val = t1.val)

edited Jan 23 '15 at 16:35

Ruskin

5,721
4
45
62

answered Jan 23 '15 at 16:10

rwbaskette

111
1

Maybe the optimizer is removing the dependency on the tvp completely? It knows it is getting a null result so discards the tvp parameter... – marknuzz Oct 24 '15 at 03:38
rwbaskette, I love you. A process that had been working fine for a long time suddenly stopped working with a timeout. using "where exists" made it all start working well again. – Ernie Thomason Feb 01 '21 at 09:59

Table valued parameters - time out

1 Answers1