表值参数-超时

本文关键字：超时 参数 值参 | 更新日期: 2023-09-27 18:00:40

我已经实现了从C#程序填充的表值参数，并使用存储过程最终存储在表中。我发现，如果我一次发送75条记录，我就不会有时间，否则我会有时间。如何一次插入更多记录？

--选择前75个主记录，其中&每个主机都有几个详细的记录

--创建了集合列表，并添加了这75个记录及其详细记录，并称为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

谢谢R

表值参数-超时

我刚刚遇到了这个问题，由于缺乏信息，我不能确定我们是否有完全相同的问题，但我会把它发布在这里，希望它能有所帮助。

我在联接中使用了表值参数。当我把它移到exists语句时，超时就消失了。我希望我有更好的理由，而不是对查询计划器的工作方式有预感。

原始-导致超时：

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

修改-作品：

select t.* 
from dbo.Table1 as t1 
where exists(select null from @table where t2.val = t1.val)
相关文章：