Solution to Sqlserver's slow insertion speed or data loss of large amounts of data

My device inserts 2,000 pieces of data into the database every second, and the two devices total 4,000. When inserting directly using the insert statement in the program, the two devices can insert a total of about 2,800 pieces at the same time, and the data is lost. There are about 1,200 items. After testing many methods, two solutions with obvious effects were sorted out:

Method 1: Use Sql Server function:

1. Combine the data into strings , use the function to insert data into the memory table, and then copy the memory table data to the table to be inserted.

2. Change the format of the combined characters: '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', Each row of data is separated by ";", and each field is separated by "|".

3. Write function:

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,&#39;&#39;+@Data+&#39;&#39;,@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,&#39;&#39;+@Data+&#39;&#39;,@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,&#39;&#39;+@Data+&#39;&#39;,@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,&#39;&#39;+@Data+&#39;&#39;,@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,&#39;&#39;+@Data+&#39;&#39;,@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,&#39;&#39;+@Data+&#39;&#39;,@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE())
        END
    SET @ii=@ii+1
  END
  -----二次循环结束
  END
 SET @i=@i+1
 END
END
RETURN
END

4. Call function statement:

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. Result display:

select * from [mytable] ;

Method 2: Use BULK INSERT

The first operation of inserting large amounts of data, use Bulk to insert file data into the database

Sql code

Create database

CREATE DATABASE [db_mgr]
GO

Create test table

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

Fill test data

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', '备注'

Open the xp_cmdshell stored procedure (open There may be security risks later)

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

Use bcp to export the format file:

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

Use bcp to export the data file:

EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T'

Clear the data in the table

truncate table db_mgr.dbo.T_Student

Use the Bulk Insert statement to batch import data files:

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

Use OPENROWSET(BULK ):

T_Student table must already exist

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

Example of using OPENROWSET (BULK):

tt table may not exist

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

For more solutions to the slow insertion speed or data loss of large amounts of data in Sqlserver, please pay attention to the PHP Chinese website for related articles!