解决Sqlserver大数据量插入速度慢或丢失数据的2种高效方法

场景背景与问题描述

在多设备并行上报数据的高压测试场景中，若单个设备每秒产生2000条记录，两个设备并发插入，使用标准的逐行INSERT语句通常仅能成功写入约2800条/秒，导致1200条左右的数据丢失。为解决这一性能瓶颈与数据一致性难题，可以采用以下两种经过验证的高效策略。

方法一：使用SQL函数解析字符串进行批量插入

该方法的核心逻辑是将多条数据组合成一个超长字符串，利用T-SQL函数按特定分隔符（行分隔符和字段分隔符）进行切片，将其拆解并插入到临时内存表，随后再从内存表复制到目标实体表。这种方法能有效减少I/O交互次数。

第1步：设计数据字符串格式

首先，需将需要插入的多行数据按照规则拼接成单一的字符串。每一行代表一条记录，不同字段间使用竖线（|）隔开，而不同的行之间则使用分号（;）作为分隔符。

示例字符串结构：
'值1|值2|值3;值1|值2|值3'
实际测试数据示例：
'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'

第2步：编写解析函数

创建一个标量值返回表类型的函数，接收原始字符串及两个分隔符参数。该函数内部采用嵌套循环的方式：外层循环以行分隔符切分行，内层循环以字段分隔符切割单行内的具体字段值，最终将结果集存入返回的Table类型变量中。

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   
BEGIN   
-- ... (此处保留完整的复杂分割算法逻辑代码，包含 WHILE 循环、CHARINDEX 定位、LEFT/RIGHT 截取及类型转换等核心处理过程)
-- 注意：原长代码块涉及复杂的字符串索引与递归处理，此处省略部分细节以保持篇幅，实际使用时请保留完整逻辑
  RETURN   
END 

第3步：调用函数执行插入

在程序端调用上述创建的函数，传入拼接好的字符串以及定义的分隔符。由于函数返回的是一个 Table 对象，可以直接通过 SELECT * FROM 将其内容 Insert 进目标表中。

insert into [mytable] select * from [dbo].[fun_funcname]('111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;...', ';', '|');

方法二：使用 BULK INSERT 命令高速导入

BULK INSERT 是 Sqlserver 提供的一种用于大规模数据批量载入的工具，其速度远快于逐行 Insert。实施此方法通常需要先生成数据的格式化文件和实际数据文件。

第1步：准备数据库环境与测试表

创建目标数据库及对应的表结构，并插入少量初始测试数据，以便后续生成正确的XML格式文件。

-- 创建数据库
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) 
);

第2步：开启 xp_cmdshell 存储过程

为了能够使用命令行工具 bcp 来导出格式化文件和导出数据文件，需要在 Sqlserver 中启用高级选项并激活 xp_cmdshell。风险提示：该功能存在极大的系统安全隐患，生产环境慎用，确保服务器网络安全隔离。

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

第3步：使用 bcp 工具生成格式文件与数据文件

通过命令行接口 bcp 分别生成一个 XML 格式的表结构映射文件（.xml）和一个二进制/文本形式的数据文件（.data）。

-- 导出格式文件
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';

第4步：清空旧数据并执行 Bulk Insert

在执行大批量导入前，务必使用 TRUNCATE TABLE 清空目标表中的残留数据。随后，在 SQL 中使用 BULK INSERT 指令指向刚才生成的数据文件，并通过 WITH 子句指定格式文件路径。

-- 清空旧数据
TRUNCATE TABLE db_mgr.dbo.T_Student;
-- 执行批量插入
BULK INSERT db_mgr.dbo.T_Student 
FROM 'C:/student.data'
WITH (
  FORMATFILE = 'C:/student_fmt.xml'
);

替代方案：使用 OPENROWSET(BULK)

除了标准的 BULK INSERT 语法，还可以利用 OPENROWSET 函数直接在 SQL 查询中访问外部数据流。这种方式更加灵活，支持在不预先建表的情况下直接将查询结果 INTO 新表，或者针对已存在的表进行定向列插入。

场景A：目标表必须已存在

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;

场景B：新建表（SELECT INTO 模式）

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 而言，面对高频产生的海量数据，传统的单条事务插入难以兼顾速度与完整性。通过构建复杂的字符解析函数或利用底层的 BULK INSERT 技术，可以将数万甚至数百万级别的耗时操作压缩至分钟级以内，彻底解决因网络抖动或锁等待导致的数据丢失问题。在实际业务选型中，若数据源为结构化文件或流式传输，推荐优先评估 BULK INSERT 的性能表现；若受限于网络无法直连物理路径，则采用内存表中转方案。

以上就是针对SqlServer大数据量插入速度慢或丢失数据的详细内容，更多关于Sqlserver批量处理和性能优化的资料请关注本站其它相关文章！