sql server 大表插入数据很慢 sql server 写入慢

转载

mob64ca1402a190 2023-10-16 15:15:40

文章标签 sql server 大表插入数据很慢 Sql server 大数据量插入速度慢 Data 数据 xml 文章分类 SQL Server 数据库

问题描述：我的设备每秒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] ;

sql server 大表插入数据很慢 sql server 写入慢_Sql server 大数据量插入速度慢

第二种：使用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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java 提交本地项目到gitlab 本地项目推送到gitlab

下一篇：MySQL查看以S开头的数据库 mysql查询以数字开头

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

sql server 大表插入数据很慢 sql server 写入慢

sql server 大表插入数据很慢 sql server 写入慢

51CTO博客