杰晶网络

针对Sqlserver大数据量插入速度慢或丢失数据的解决方法
数据库 2024/12/26 佚名
3 2

我的设备上每秒将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

4.调用函数语句：

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',';','|');

5.结果展示：

select * from [mytable] ;

方法二：使用BULK INSERT

大数据量插入第一种操作，使用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', '备注'

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

EXEC sp_configure 'show advanced options', 1; 
RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1; 
EXEC sp_configure 'show advanced options', 0; 
RECONFIGURE;

使用bcp导出格式文件：

EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'

使用bcp导出数据文件：

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语句批量导入数据文件：

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

使用OPENROWSET(BULK)的例子：

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

使用OPENROWSET(BULK)的例子：

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
标签：
Sqlserver,大数据量,插入,速度慢,丢失数据,解决方法
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
www.imxmx.com 杰晶网络
8,675无损音乐
1,324高清电影
213破解软件
120,141站长资源

P70系列延期，华为新旗舰将在下月发布

3月20日消息，近期博主@数码闲聊站 透露，原定三月份发布的华为新旗舰P70系列延期发布，预计4月份上市。

而博主@定焦数码 爆料，华为的P70系列在定位上已经超过了Mate60，成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢？

根据目前爆料的消息来看，华为P70系列将推出三个版本，其中P70和P70 Pro采用了三角形的摄像头模组设计，而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智，但辨识度绝对拉满。

更新动态

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网