登录
当前位置： 首页 > 编程笔记 >

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

史鹏云
2023-03-14
本文向大家介绍针对Sqlserver大数据量插入速度慢或丢失数据的解决方法，包括了针对Sqlserver大数据量插入速度慢或丢失数据的解决方法的使用技巧和注意事项，需要的朋友参考一下

我的设备上每秒将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
 类似资料：
  • 快速解决mysql导数据时,格式不对、导入慢、丢数据的问题

    本文向大家介绍快速解决mysql导数据时,格式不对、导入慢、丢数据的问题，包括了快速解决mysql导数据时,格式不对、导入慢、丢数据的问题的使用技巧和注意事项，需要的朋友参考一下 如果希望一劳永逸的解决慢的问题，不妨把你的mysql升级到mysql8.0吧，mysql8.0默认的字符集已经从latin1改为utf8mb4，因此现在UTF8的速度要快得多，在特定查询时速度提高了1800％！ 但是如果

  • 详解C#批量插入数据到Sqlserver中的四种方式

    本文向大家介绍详解C#批量插入数据到Sqlserver中的四种方式，包括了详解C#批量插入数据到Sqlserver中的四种方式的使用技巧和注意事项，需要的朋友参考一下 本篇，我将来讲解一下在Sqlserver中批量插入数据。 先创建一个用来测试的数据库和表，为了让插入数据更快，表中主键采用的是GUID，表中没有创建任何索引。GUID必然是比自增长要快的，因为你生成一个GUID算法所花的时间肯定比你

  • 批量插入大量数据到数据库

    我创建了一个向MySql数据库插入数百万个值的程序。我读到过有关批插入的文章，它将优化我的程序并使其更快，但当我尝试这样做时，它以同样的方式工作。我没有将每个值插入数据库，而是每次将500个值保存在一个列表中，然后将它们插入一个大循环中，如下所示： 然后我删除列表中的所有值，并再次开始收集500个值。它不应该工作得更好吗？ 我的插入代码是： 我有一些问题： 1。为什么当我批量插入时它不能更快地工作

  • C#批量插入数据到Sqlserver中的三种方式

    本文向大家介绍C#批量插入数据到Sqlserver中的三种方式，包括了C#批量插入数据到Sqlserver中的三种方式的使用技巧和注意事项，需要的朋友参考一下 本篇，我将来讲解一下在Sqlserver中批量插入数据。 先创建一个用来测试的数据库和表，为了让插入数据更快，表中主键采用的是GUID，表中没有创建任何索引。GUID必然是比自增长要快的，因为你生成一个GUID算法所花的时间肯定比你从数据表

  • Django数据库连接丢失问题的解决方法

    本文向大家介绍Django数据库连接丢失问题的解决方法，包括了Django数据库连接丢失问题的解决方法的使用技巧和注意事项，需要的朋友参考一下 问题 在Django中使用mysql偶尔会出现数据库连接丢失的情况，错误通常有如下两种 OperationalError: (2006, 'MySQL server has gone away') OperationalError: (2013, 'Los

  • Eclipse安装ADT插件速度慢的解决方法

    本文向大家介绍Eclipse安装ADT插件速度慢的解决方法，包括了Eclipse安装ADT插件速度慢的解决方法的使用技巧和注意事项，需要的朋友参考一下 本文讲述了Eclipse安装ADT插件速度慢的解决方法。分享给大家供大家参考，具体如下： 在天朝很多事情就不可避免的蛋疼，download.eclipse.org 访问巨慢，导致 ADT 安装过程也巨慢，有事运气好会快一点，但大部分时候就跟shit

相关阅读

Eclipse安装ADT插件速度慢的解决方法php快速导入大量数据的实例方法插入sql数据库时处理大量数据vuex页面刷新导致数据丢失的解决方案SqlServer数据库中文乱码问题解决方法

相关文章

PHP MySQL 插入数据百度大数据一面百度 大数据一面百度 大数据一面百度 大数据一面

相关问答

UDP数据包大小和数据包丢失Primefaces数据表丢失数据FSharp。数据丢失方法异常撤销数据丢失对PostgreSQL数据库中同一个表的同时写入速度较慢

相关工具

SPT 对象数据库天气数据解析解析html网页的数据标准TVL格式数据解析Flurry统计数据ANE

相关文档

大数据入门指南IBM DB2 数据库快速入门数据挖掘十大算法大数据实验手册大数据学习指南
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.