针对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
登录后复制
更多针对Sqlserver大数据量插入速度慢或丢失数据的解决方法相关文章请关注PHP中文网！
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
1 周前
By DDD
R.E.P.O.如果您听不到任何人，如何修复音频
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
在哪里可以找到原子中的起重机控制钥匙卡
1 周前
By DDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
在Laravel中使用Flash会话数据
Mar 12, 2025 pm 05:08 PM
Laravel使用其直观的闪存方法简化了处理临时会话数据。这非常适合在您的应用程序中显示简短的消息，警报或通知。 默认情况下，数据仅针对后续请求： $请求 -
php中的卷曲：如何在REST API中使用PHP卷曲扩展
Mar 14, 2025 am 11:42 AM
PHP客户端URL（curl）扩展是开发人员的强大工具，可以与远程服务器和REST API无缝交互。通过利用Libcurl（备受尊敬的多协议文件传输库），PHP curl促进了有效的执行
PHP记录：PHP日志分析的最佳实践
Mar 10, 2025 pm 02:32 PM
PHP日志记录对于监视和调试Web应用程序以及捕获关键事件，错误和运行时行为至关重要。它为系统性能提供了宝贵的见解，有助于识别问题并支持更快的故障排除
简化的HTTP响应在Laravel测试中模拟了
Mar 12, 2025 pm 05:09 PM
Laravel 提供简洁的 HTTP 响应模拟语法，简化了 HTTP 交互测试。这种方法显着减少了代码冗余，同时使您的测试模拟更直观。 基本实现提供了多种响应类型快捷方式： use Illuminate\Support\Facades\Http; Http::fake([ 'google.com' => 'Hello World', 'github.com' => ['foo' => 'bar'], 'forge.laravel.com' =>
在Codecanyon上的12个最佳PHP聊天脚本
Mar 13, 2025 pm 12:08 PM
您是否想为客户最紧迫的问题提供实时的即时解决方案？ 实时聊天使您可以与客户进行实时对话，并立即解决他们的问题。它允许您为您的自定义提供更快的服务
解释PHP中晚期静态结合的概念。
Mar 21, 2025 pm 01:33 PM
文章讨论了PHP 5.3中引入的PHP中的晚期静态结合（LSB），从而允许静态方法的运行时分辨率调用以获得更灵活的继承。 LSB的实用应用和潜在的触摸
