用 Table Valued Parameter 往 sql server 插数据

在维护以前的程序时，遇到这样的情况，需要向远程数据库写非常多的记录。

原先的实现是这样的 ：

一 ： 用datagridview显示要写入的记录，假设有10000条

二：  循环每一条记录，调用存储过程，将该条记录写入 ， 代码如下

            for (int i = 0; i < dgv_compare.Rows.Count; i++)  
            {
                bool import = false;
                RatePlanRow row = ReadRow(dgv_compare, i, ref import);
                if (!import)
                    continue;
 
                if (!DBCommands.sp_app_RatePlanDetail_Add(
                    CarrierID,
                    is_supplier == 1 ? 0 : 1,
                    row.Prefix,
                    row.RegionName,
                    row.NewRate,
                    row.FreeBlock,
                    row.InitialBlock,
                    row.RecycleBlock,
                    row.ConnectionCharge,
                    row.EnrollDate,
                    row.ExpiryDate,
                    row.isDisabled,
                    row.TimeF1,
                    row.TimeT1,
                    row.Rate1,
                    row.Block1Enabled,
                    row.TimeF2,
                    row.TimeT2,
                    row.Rate2,
                    row.Block2Enabled,
                    row.TimeF3,
                    row.TimeT3,
                    row.Rate3,
                    row.Block3Enabled,
                    Operator,
                    FlagShortOverLong,
                    ref ErrCode,
                    ref ErrMsg))
                {
                    //tb_log.Text += DBCommands.LastError + "
";
                    MessageBox.Show(DBCommands.LastError);
                    return;
                }
               
                OnProcess(Convert.ToInt32(i * 100.0 / dgv_compare.Rows.Count));
            }

 这里调用的存储过程  sp_app_RatePlanDetail_Add ， 大致实现是这样的，

• 根据传入的每一条记录， 先做一些update 和 delete的操作， 例如

    

			update [RatePlan_Detail] set expiryDate = dateadd(second,-1, @EnrollDate) where rateplanid = @RatePlanID and prefix like (@Prefix + '%') and ExpiryDate > @EnrollDate

设 传进来的记录为  row

意思是找出当前表中所有rateplanid 和row的@RatePlanID相同  ，且ExpiryDate大于row的@EnrollDate的记录

然后update 这些记录的 expiryDate  

• 然后再insert row

--------------------------------以上是原先的实现方法 ， 也就是说，如果有10000条记录，那么会调用10000次存储过程。

当运行这段代码时， 我发现速度非常慢，数据一旦上万， 经常运行1-2小时，所以就想改进一下，把速度提高。

最初的想法是觉得这句代码有问题

update [RatePlan_Detail] set expiryDate = dateadd(second,-1, @EnrollDate) where rateplanid = @RatePlanID and prefix like (@Prefix + '%') and ExpiryDate > @EnrollDate

 我认为表索引建的不好，update效率不高。

所以，我在测试的时候把  sp_app_RatePlanDetail_Add 这个存储过程里面的代码全部注释，只写非常简单的一句，例如

select @a=1

 然后运行程序，以此验证是不是存储过程本身不科学，效率低。

但奇怪的是： 程序执行的时间貌似一点没少，还是和以前差不多。 想不出其他办法，上stackoverflow发帖求助。

帖子地址：

http://stackoverflow.com/questions/18693590/using-stored-procedure-to-insert-many-records-into-database-one-by-one-but-it-i

发帖后，有人回答说：

i thought this maybe because the unoptimize update or delete, so i try to change the procedure sp_app_RatePlanDetail_Add_0909 , let it only contain one statement, like this :

select@a=1

but it is very strange, the speed of whole procedure seems does not change at all, it still spend much time , can anybody tell me what is the reason ? thanks.

Emphasis mine.

This would indicate that your problem is related to latency (alternatively, that the performance of your stored procedure is not where the performance problem is; consider looking elsewhere). That is to say, the time to send the request to execute the stored procedure and receive the results is much greater than the time it takes to execute the stored procedure.

Because you call the stored procedure once per record, the time "wasted" due to latency is increased by a factor of 8,000.

You can significantly reduce the number of round-trips by using table valued parameters to stream many records to the stored procedure at once.

Note that using table valued parameters isn't a panacea, your stored procedures may suffer poor performance due to parameter sniffing.

意思是说时间全浪费在10000条记录的request和receive上，存储过程本身问题倒并不大

此人给出的建议是：

You can significantly reduce the number of round-trips by using table valued parameters to stream many records to the stored procedure at once.

关于table valued parameters ，例子网站是 

http://mikesdotnet.wordpress.com/2013/03/17/inserting-data-into-a-sql-server-database-using-a-table-valued-parameter/

需FQ

按照这个网站的介绍，使用 table value parameter 的大致方法是

1： 建一个测试表

CREATE TABLE [dbo].[Person](
	[ID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[Gender] [int] NOT NULL,
 CONSTRAINT [PK_Person_1] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2： 在sql server 数据库的 Programmablity----------->Types------------->User-Defined Table Types

建一个 user define table type , 此类型的成员要与想插入的表的成员一致

CREATE TYPE [dbo].[Person_TVP] AS TABLE(
	[ID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[DateOfBirth] [datetime] NULL,
	[Gender] [int] NULL,
	PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

3 ： 数据库加一个存储过程，接受 table value parameter当参数 ， 例如

CREATE PROCEDURE [dbo].[BulkPerson_Insert]
(
	@TVP dbo.Person_TVP READONLY
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        INSERT INTO Person
        (
		[ID]
		,[FirstName]
		,[LastName]
		,[DateOfBirth]
		,[Gender]
	)
	SELECT [ID]
		,[FirstName]
		,[LastName]
		,[DateOfBirth]
		,[Gender]
	FROM @TVP tvp
END

4: 在c#程序中 ， 建一个类

public class Person
{
	public Guid ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DateOfBirth { get; set; }
        public int Gender { get; set; }
}

定义这个类的List ， 包含50000个实例

// create the collection of people
List<Person> people = new List<Person>();

// create 50000
for (int i = 0; i < 50000; i++)
{
people.Add(new Person()
{
    ID = Guid.NewGuid(),
    FirstName = "test",
    LastName = "test",
    DateOfBirth = DateTime.Now,
    Gender = 1
});
}

调用存储过程的c#函数

private static void DoBulkInsert(List<Person> people)
{
	using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
        {
        	conn.Open();

                using (SqlCommand comm = conn.CreateCommand())
                {
                    comm.CommandText = "BulkPerson_Insert";
                    comm.CommandType = System.Data.CommandType.StoredProcedure;
                    comm.CommandTimeout = 60;
                    comm.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@TVP",
                        SqlDbType = SqlDbType.Structured, 
                        Value = GetDataTableParam(people)                   //注意这里调用另外一个函数设置value
                    });

                    // time how long it takes
                    Stopwatch sw = new System.Diagnostics.Stopwatch();
                    sw.Start();

                    // run the query
                    comm.ExecuteNonQuery();

                    sw.Stop();

                    // output the time taken
                    Console.WriteLine("Bulk insert takes - " + sw.Elapsed.TotalSeconds + " seconds");
                }
	}
}

上面函数中调用的函数

/// <summary>
/// Create a data table we can pass to the tvp
/// </summary>
/// <param name="People"></param>
/// <returns></returns>
private static DataTable GetDataTableParam(List<Person> People)
{ 
        define the table and rows (the rows match those in the TVP)
	DataTable peopleTable = new DataTable();
	peopleTable.Columns.Add("ID", typeof(Guid));
        peopleTable.Columns.Add("FirstName", typeof(string));
        peopleTable.Columns.Add("LastName", typeof(string));
        peopleTable.Columns.Add("DateOfBirth", typeof(DateTime));
        peopleTable.Columns.Add("Gender", typeof(int));

	foreach (Person p in People)
        {
                // add a row for each person
        	DataRow row = peopleTable.NewRow();
                row["ID"] = p.ID;
                row["FirstName"] = p.FirstName;
                row["LastName"] = p.LastName;
                row["DateOfBirth"] = p.DateOfBirth;
                row["Gender"] = p.Gender;
                peopleTable.Rows.Add(row);	
	}
	return peopleTable;
}

经这样处理后，速度比以前约提高三倍。

stackoverflow的回帖人最后说 ： Note that using table valued parameters isn't a panacea, your stored procedures may suffer poor performance due to parameter sniffing.

parameter sniffing 还不是很清楚，以后再研究。