针对Sqlserver大数据量插入速度慢或丢失数据的解决方法

2024-04-10 16:22:04 22

我的设备上每秒将2000条数据插入数据库，2个设备总共4000条，当在程序里面直接用insert语句插入时，两个设备同时插入大概总共能插入约2800条左右，数据丢失约1200条左右，测试了很多方法，整理出了两种效果比较明显的解决办法：

方法一：使用SqlServer函数:

1.将数据组合成字串，使用函数将数据插入内存表，后将内存表数据复制到要插入的表。

2.组合成的字符换格式：'111|222|333|456,7894,7458|0|1|2014-01-0112:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-0112:15:16',每行数据中间用“;”隔开，每个字段之间用“|”隔开。

3.编写函数：

CREATEFUNCTION[dbo].[fun_funcname](@strVARCHAR(max),@splitcharCHAR(1),@splitchar2CHAR(1))
--定义返回表
RETURNS@tTABLE(MaxValuefloat,Phaseint,SlopeValuefloat,Datavarchar(600),Alarmint,AlmLevint,GpsTimedatetime,UpdateTimedatetime)AS
/*
author:hejunli
createdate:2014-06-09
*/
BEGIN
DECLARE@substrVARCHAR(max),@substr2VARCHAR(max)
--申明单个接收值
declare@MaxValuefloat,@Phaseint,@SlopeValuefloat,@Datavarchar(8000),@Alarmint,@AlmLevint,@GpsTimedatetime
SET@substr=@str
DECLARE@iINT,@jINT,@iiINT,@jjINT,@ijj1int,@ijj2int,@mint,@mmint
SET@j=LEN(REPLACE(@str,@splitchar,REPLICATE(@splitchar,2)))-LEN(@str)--获取分割符个数
IF@j=0
BEGIN
--INSERTINTO@tVALUES(@substr,1)--没有分割符则插入整个字串
set@substr2=@substr;
set@ii=0
SET@jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
WHILE@ii<=@jj
BEGIN
if(@ii<@jj)
begin
SET@mm=CHARINDEX(@splitchar2,@substr2)-1--获取分割符的前一位置
if(@ii=0)
set@MaxValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=1)
set@Phase=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=2)
set@SlopeValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=3)
set@Data=cast(LEFT(@substr2,@mm)asvarchar)
elseif(@ii=4)
set@Alarm=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=5)
set@AlmLev=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=6)
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
SET@substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1))--去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
--当循环到最后一个值时将数据插入表
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
END
--END
SET@ii=@ii+1
END
END
ELSE
BEGIN
SET@i=0
WHILE@i<=@j
BEGIN
IF(@i<@j)
BEGIN
SET@m=CHARINDEX(@splitchar,@substr)-1--获取分割符的前一位置
--INSERTINTO@tVALUES(LEFT(@substr,@m),@i+1)
-----二次循环开始
--1.线获取要二次截取的字串
set@substr2=(LEFT(@substr,@m));
--2.初始化二次截取的起始位置
set@ii=0
--3.获取分隔符个数
SET@jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
WHILE@ii<=@jj
BEGIN
if(@ii<@jj)
begin
SET@mm=CHARINDEX(@splitchar2,@substr2)-1--获取分割符的前一位置
if(@ii=0)
set@MaxValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=1)
set@Phase=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=2)
set@SlopeValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=3)
set@Data=cast(LEFT(@substr2,@mm)asvarchar)
elseif(@ii=4)
set@Alarm=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=5)
set@AlmLev=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=6)
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
SET@substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1))--去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
--当循环到最后一个值时将数据插入表
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
END
--END
SET@ii=@ii+1
END
-----二次循环结束
SET@substr=RIGHT(@substr,LEN(@substr)-(@m+1))--去除已获取的分割串，得到还需要继续分割的字符串
END
ELSE
BEGIN
--INSERTINTO@tVALUES(@substr,@i+1)--对最后一个被分割的串进行单独处理
-----二次循环开始
--1.线获取要二次截取的字串
set@substr2=@substr;
--2.初始化二次截取的起始位置
set@ii=0
--3.获取分隔符个数
SET@jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--获取分割符个数
WHILE@ii<=@jj
BEGIN
if(@ii<@jj)
begin
SET@mm=CHARINDEX(@splitchar2,@substr2)-1--获取分割符的前一位置
if(@ii=0)
set@MaxValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=1)
set@Phase=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=2)
set@SlopeValue=cast(LEFT(@substr2,@mm)asfloat)
elseif(@ii=3)
set@Data=cast(LEFT(@substr2,@mm)asvarchar)
elseif(@ii=4)
set@Alarm=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=5)
set@AlmLev=cast(LEFT(@substr2,@mm)asint)
elseif(@ii=6)
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
SET@substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1))--去除已获取的分割串，得到还需要继续分割的字符串
end
else
BEGIN
--当循环到最后一个值时将数据插入表
INSERTINTO@tVALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2asdatetime),GETDATE())
END
SET@ii=@ii+1
END
-----二次循环结束
END
SET@i=@i+1
END
END
RETURN
END

4.调用函数语句：

insertinto[mytable]select*from[dbo].[fun_funcname]('111|222|333|456,7894,7458|0|1|2014-01-0112:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-0112:15:16',';','|');

5.结果展示：

select*from[mytable];

方法二：使用BULKINSERT

大数据量插入第一种操作，使用Bulk将文件数据插入数据库

Sql代码

创建数据库

CREATEDATABASE[db_mgr]
GO

创建测试表

USEdb_mgr
CREATETABLEdbo.T_Student(
F_ID[int]IDENTITY(1,1)NOTNULL,
F_Codevarchar(10),
F_Namevarchar(100),
F_Memonvarchar(500),
F_Memo2ntext,
PRIMARYKEY(F_ID)
)
GO

填充测试数据

InsertIntoT_Student(F_Code,F_Name,F_Memo,F_Memo2)select
'code001','name001','memo001','备注'unionallselect
'code002','name002','memo002','备注'unionallselect
'code003','name003','memo003','备注'unionallselect
'code004','name004','memo004','备注'unionallselect
'code005','name005','memo005','备注'unionallselect
'code006','name006','memo006','备注'

开启xp_cmdshell存储过程（开启后有安全隐患）

EXECsp_configure'showadvancedoptions',1;
RECONFIGURE;EXECsp_configure'xp_cmdshell',1;
EXECsp_configure'showadvancedoptions',0;
RECONFIGURE;

使用bcp导出格式文件：

EXECmaster..xp_cmdshell'BCPdb_mgr.dbo.T_Studentformatnul-fC:/student_fmt.xml-x-c-T'

使用bcp导出数据文件：

EXECmaster..xp_cmdshell'BCPdb_mgr.dbo.T_StudentoutC:/student.data-fC:/student_fmt.xml-T'

将表中数据清空

truncatetabledb_mgr.dbo.T_Student

使用BulkInsert语句批量导入数据文件：

BULKINSERTdb_mgr.dbo.T_Student
FROM'C:/student.data'
WITH
(
FORMATFILE='C:/student_fmt.xml'
)

使用OPENROWSET(BULK)的例子：

T_Student表必须已存在

INSERTINTOdb_mgr.dbo.T_Student(F_Code,F_Name)SELECTF_Code,F_Name
FROMOPENROWSET(BULKN'C:/student.data',FORMATFILE=N'C:/student_fmt.xml')ASnew_table_name

使用OPENROWSET(BULK)的例子：

tt表可以不存在

SELECTF_Code,F_NameINTOdb_mgr.dbo.tt
FROMOPENROWSET(BULKN'C:/student.data',FORMATFILE=N'C:/student_fmt.xml')ASnew_table_name

针对Sqlserver大数据量插入速度慢或丢失数据的解决方法

热门推荐

随机推荐