针对Sqlserver大数据量插入速度慢或丢失数据的解决方法
16lz
2022-01-10
我的设备上每秒将2000条数据插入数据库,2个设备总共4000条,当在程序里面直接用insert语句插入时,两个设备同时插入大概总共能插入约2800条左右,数据丢失约1200条左右,测试了很多方法,整理出了两种效果比较明显的解决办法:
方法一:使用Sql Server函数:
1.将数据组合成字串,使用函数将数据插入内存表,后将内存表数据复制到要插入的表。
2.组合成的字符换格式:'111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16',每行数据中间用“;”隔开,每个字段之间用“|”隔开。
3.编写函数:
CREATE FUNCTION [dbo].[fun_funcname](@str VARCHAR(max),@splitchar CHAR(1),@splitchar2 CHAR(1)) --定义返回表 RETURNS @t TABLE(MaxValue float,Phase int,SlopeValue float,Data varchar(600),Alarm int,AlmLev int,GpsTime datetime,UpdateTime datetime) AS /* author:hejun li create date:2014-06-09 */ BEGIN DECLARE @substr VARCHAR(max),@substr2 VARCHAR(max)--申明单个接收值 declare @MaxValue float,@Phase int,@SlopeValue float,@Data varchar(8000),@Alarm int,@AlmLev int,@GpsTime datetime SET @substr=@str DECLARE @i INT,@j INT,@ii INT,@jj INT,@ijj1 int,@ijj2 int,@m int,@mm int SET @j=LEN(REPLACE(@str,@splitchar,REPLICATE(@splitchar,2)))-LEN(@str)--获取分割符个数 IF @j=0 BEGIN --INSERT INTO @t VALUES (@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) as float) else if(@ii=1) set @Phase=cast(LEFT(@substr2,@mm) as int) else if(@ii=2) set @SlopeValue=cast(LEFT(@substr2,@mm) as float) else if(@ii=3) set @Data=cast(LEFT(@substr2,@mm) as varchar) else if(@ii=4) set @Alarm=cast(LEFT(@substr2,@mm) as int) else if(@ii=5) set @AlmLev=cast(LEFT(@substr2,@mm) as int) else if(@ii=6) INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串,得到还需要继续分割的字符串 end else BEGIN --当循环到最后一个值时将数据插入表 INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),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 --获取分割符的前一位置 --INSERT INTO @t VALUES(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) as float) else if(@ii=1) set @Phase=cast(LEFT(@substr2,@mm) as int) else if(@ii=2) set @SlopeValue=cast(LEFT(@substr2,@mm) as float) else if(@ii=3) set @Data=cast(LEFT(@substr2,@mm) as varchar) else if(@ii=4) set @Alarm=cast(LEFT(@substr2,@mm) as int) else if(@ii=5) set @AlmLev=cast(LEFT(@substr2,@mm) as int) else if(@ii=6) INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串,得到还需要继续分割的字符串 end else BEGIN --当循环到最后一个值时将数据插入表 INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) END --END SET @ii=@ii+1 END -----二次循环结束 SET @substr=RIGHT(@substr,LEN(@substr)-(@m+1)) --去除已获取的分割串,得到还需要继续分割的字符串 END ELSE BEGIN --INSERT INTO @t VALUES(@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) as float) else if(@ii=1) set @Phase=cast(LEFT(@substr2,@mm) as int) else if(@ii=2) set @SlopeValue=cast(LEFT(@substr2,@mm) as float) else if(@ii=3) set @Data=cast(LEFT(@substr2,@mm) as varchar) else if(@ii=4) set @Alarm=cast(LEFT(@substr2,@mm) as int) else if(@ii=5) set @AlmLev=cast(LEFT(@substr2,@mm) as int) else if(@ii=6) INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) SET @substr2=RIGHT(@substr2,LEN(@substr2)-(@mm+1)) --去除已获取的分割串,得到还需要继续分割的字符串 end else BEGIN --当循环到最后一个值时将数据插入表 INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) END SET @ii=@ii+1 END -----二次循环结束 END SET @i=@i+1 END END RETURN END
insert into [mytable] select * from [dbo].[fun_funcname]('111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16',';','|');
select * from [mytable] ;
大数据量插入第一种操作,使用Bulk将文件数据插入数据库
Sql代码
创建数据库
CREATE DATABASE [db_mgr] GO
USE db_mgr CREATE TABLE dbo.T_Student( F_ID [int] IDENTITY(1,1) NOT NULL, F_Code varchar(10) , F_Name varchar(100) , F_Memo nvarchar(500) , F_Memo2 ntext , PRIMARY KEY (F_ID) ) GO
Insert Into T_Student(F_Code, F_Name, F_Memo, F_Memo2) select'code001', 'name001', 'memo001', '备注' union all select'code002', 'name002', 'memo002', '备注' union all select'code003', 'name003', 'memo003', '备注' union all select'code004', 'name004', 'memo004', '备注' union all select'code005', 'name005', 'memo005', '备注' union all select'code006', 'name006', 'memo006', '备注'
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1; EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T'
truncate table db_mgr.dbo.T_Student
BULK INSERT db_mgr.dbo.T_Student FROM 'C:/student.data'WITH( FORMATFILE = 'C:/student_fmt.xml')
T_Student表必须已存在
INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name) SELECT F_Code, F_Name FROM OPENROWSET(BULK N'C:/student.data', FORMATFILE=N'C:/student_fmt.xml') AS new_table_name
tt表可以不存在
SELECT F_Code, F_Name INTO db_mgr.dbo.tt FROM OPENROWSET(BULK N'C:/student.data', FORMATFILE=N'C:/student_fmt.xml') AS new_table_name
更多相关文章
- MySQL 5.7.9 服务无法启动-“NET HELPMSG 3534”的解决方法
- mybatisplus的坑 insert标签insert into select无参数问题的解决
- python起点网月票榜字体反爬案例
- 《Android开发从零开始》——25.数据存储(4)
- Android系统配置数据库注释(settings.db)
- Android中不同应用间实现SharedPreferences数据共享
- android图表ichartjs
- Android内容提供者源码
- android SharedPreferences