7.数据库存储(LEVELI&II)的难点：高速数据插入
请关注个人小站：http://sqlhis.com/
笔者在这个项目中使用的是SQL SERVER数据库。
网络上搜索“SQL SERVER高速数据插入”，可以获得很多结果。大致上是：
.使用C#的SqlBulkCopy类，这个插入功能确实速度不错，但是我对底层的实现很疑惑，怀疑可能是一个特殊的接口，当然这个以后可以探索一下。
.使用Bulk Insert，这需要生成特定格式的文件，然后通过文件导入，一次性导入还好，想做到程序里面就相当困难了
.直接使用insert语句，把insert串起来，多个insert做成一个事务。这个方法不会太快。
笔者想介绍一下使用用户定义表类型（User-Defined Table Types）配合存储过程来进行插入，这是我目前测试的最高速的插入。
第一步：建立一个用户自定义表类型，代码如下：
CREATE TYPE dbo.udtt_TickData AS TABLE
(
SecId int NOT NULL,
BizDate int NOT NULL,
TradeTime int NOT NULL,
Price int NULL
PRIMARY KEY(SecId,BizDate,TradeTime)
)
测试一下这个用户定义表类型
--声明一个变量@t,类型为AType
declare @t udtt_TickData--往变量@t中插入2条数据
insert into @t (SecId,BizDate,TradeTime,Price)
select 1,20200101,9030000,1
union all
select 1,20200101,9030001,1
--显示变量@t中数据
select * from @t
第二步，建立一个表TickData容纳数据
CREATE TABLE TickData
(
SecId int NOT NULL,
BizDate int NOT NULL,
TradeTime int NOT NULL,
Price int NULL
PRIMARY KEY(SecId,BizDate,TradeTime)
)
第三步，建立存储过程接收从用户自定义表类型传入的数据
CREATE PROCEDURE [dbo].updTickData
@p_ImportData udtt_TickData READONLY --输入表值参数
AS
BEGIN
INSERT TickData
SELECT * FROM @p_ImportData
END
第四步，通过存储过程插入数据
--声明一个变量@t,类型为AType
declare @t udtt_TickData--往变量@t中插入2条数据
insert into @t (SecId,BizDate,TradeTime,Price)
select 1,20200101,9030000,1
union all
select 1,20200101,9030001,1
--清除掉TickData的数据
TRUNCATE TABLE TickData
--显示变量@t中数据
exec updTickData @t
select * from TickData
完成了以上4步，一个简单的通过用户定义表类型插入的范例就完成了。我们还没有使用程序写入，我们继续构建一个C#程序，C#程序构造一个DataTable，作为存储过程的参数插入即可。
使用C#构建一个控制台应用程序
static void Main(string[] args)
{
using (SqlConnection connTarget = new SqlConnection("Data Source=192.168.3.22;Initial Catalog=TESTDB;User ID=sa;Password=XXXXX"))
{
connTarget.Open();
SqlCommand commond = connTarget.CreateCommand();//构建DataTable
DataTable dt = new DataTable();
dt.Columns.Add("SecId", Type.GetType("System.Int32"));
dt.Columns.Add("BizDate", Type.GetType("System.Int32"));
dt.Columns.Add("TradeTime", Type.GetType("System.Int32"));
dt.Columns.Add("Price", Type.GetType("System.Int32"));
DataRow row = dt.NewRow();
row[0] = 1;
row[1] = 20200101;
row[2] = 9030000;
row[3] = 1;
dt.Rows.Add(row);
DataRow row2 = dt.NewRow();
row2[0] = 1;
row2[1] = 20200101;
row2[2] = 9030001;
row2[3] = 1;
dt.Rows.Add(row2);
//DataTable作为参数传入
commond.CommandType = CommandType.StoredProcedure;
commond.CommandText = "updTickData";
commond.Parameters.AddWithValue("@p_ImportData", dt);
commond.ExecuteNonQuery();
connTarget.Close();
}
}
全部完成，在实际应用中，注意每个构造的DataTable的行数需要多一点，几千到几万行提交一次会比较好，具体如何选择可以在实际程序中进行测试，选定最佳行数。
注意：使用此方法还有一个优点，当DataTable传入存储过程中后，后续的处理自由度大，可以是单独的INSERT，也可以使用MERGE，甚至更多更复杂的语句。
