Solutions to slow insertion or data loss of SQL Server large data volumes

My device inserts 2000 data records into the database every second, and 2 devices have a total of 4000 records. When the insert statement is directly used in the program, about 2800 pieces of data can be inserted at the same time between the two devices, and about 1200 pieces of data are lost. Many methods have been tested and two effective solutions have been found:

Method 1: Use the SQL Server function:

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

2. converted character format: '2017 | 111 | 222 | 333 | 456,7894, 7458 | 0 | 1 | 12:15:16; 1111 | 2222 | 3333 | 456,7894, 7458 | 0 | 1 | 12:15:16 '. each row of data is separated by ";", and each field is separated by "|.

3. Compile functions:

Create function [dbo]. [fun_funcname] (@ str VARCHAR (max), @ splitchar CHAR (1), @ splitchar2 CHAR (1) -- Define the return table returns @ t TABLE (MaxValue float, Phase int, slopeValue float, Data varchar (600), Alarm int, almlevint, GpsTime datetime, UpdateTime datetime) AS/* author: hejun li create date: 2014-06-09 */begin declare @ substr VARCHAR (max), @ substr2 VARCHAR (max) -- declare a single receipt value DECLARE @ MaxValue float, @ Phase int, @ SlopeValue float, @ Data varchar (8000), @ Alarm int, @ almlevint, @ 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) -- get the number of delimiters IF @ j = 0 BEGIN -- insert into @ t VALUES (@ substr, 1) -- if there is no delimiter, insert the entire string set @ substr2 = @ substr; set @ ii = 0 SET @ jj = LEN (REPLACE (@ substr2, @ splitchar2, REPLICATE (@ splitchar2, (2)-LEN (@ substr2) -- get the number of delimiters WHILE @ ii <= @ jj BEGIN if (@ ii <@ jj) begin SET @ mm = CHARINDEX (@ splitchar2, @ substr2)-1 -- get the first position of the delimiter 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) -- remove the obtained split string, get the end else BEGIN string that needs to be further split -- INSERT the data INTO the table insert into @ t VALUES (@ MaxValue, @ Phase, @ SlopeValue, ''+ @ Data + '', @ Alarm, @ almlev, cast (@ substr2 as datetime), GETDATE ()) END -- end set @ ii = @ ii + 1 end else begin set @ I = 0 WHILE @ I <= @ j begin if (@ I <@ j) begin set @ m = CHARINDEX (@ splitchar, @ substr)-1 -- get the previous position of the delimiter -- insert into @ t VALUES (LEFT (@ substr, @ m ), @ I + 1) ----- the start of the secondary loop -- 1. line to obtain the string set @ substr2 = (LEFT (@ substr, @ m); -- 2. initialize the starting position of the second truncation. set @ ii = 0 -- 3. get the number of separators SET @ jj = LEN (REPLACE (@ substr2, @ splitchar2, REPLICATE (@ splitchar2, 2)-LEN (@ substr2) -- Get the number of delimiter WHILE @ ii <= @ jj BEGIN if (@ ii <@ jj) begin SET @ mm = CHARINDEX (@ splitchar2, @ substr2) -1 -- get the first part of the delimiter 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) -- remove the obtained split string, get the end else BEGIN string that needs to be further split -- INSERT the data INTO the table 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 )) -- remove the obtained split string to obtain the end else begin -- insert into @ t VALUES (@ substr, @ I + 1) string to be further split) -- separate the last split string ----- the start of the second loop -- 1. obtain the string set @ substr2 = @ substr; -- 2. initialize the starting position of the second truncation. set @ ii = 0 -- 3. get the number of separators SET @ jj = LEN (REPLACE (@ substr2, @ splitchar2, REPLICATE (@ splitchar2, 2)-LEN (@ substr2) -- Get the number of delimiter WHILE @ ii <= @ jj BEGIN if (@ ii <@ jj) begin SET @ mm = CHARINDEX (@ splitchar2, @ substr2) -1 -- get the first part of the delimiter 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) -- remove the obtained split string, get the end else BEGIN string that needs to be further split -- INSERT the data INTO the table insert into @ t VALUES (@ MaxValue, @ Phase, @ SlopeValue, ''+ @ Data + '', @ Alarm, @ almlev, cast (@ substr2 as datetime), GETDATE ()) end set @ ii = @ ii + 1 END ----- END of the quadratic loop end set @ I = @ I + 1 END RETURN END

4. Call the 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

Insert large data volume in the first operation. Use Bulk to insert file data into the database.

SQL code

Create a 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 in Test Data

Insert Into T_Student (F_Code, F_Name, F_Memo, F_Memo2) select 'code001', 'name001', 'mo001', 'note' union all select 'code002', 'name002 ', 'memo002 ', 'note' union all select 'code003', 'name003 ', 'memo003', 'note' union all select 'code004 ', 'name004', 'memo004 ', 'note' union all select 'code005 ', 'name005', 'memo005 ', 'note' union all select 'code006', 'name006 ', 'memo006', 'note'

Enable the xp_mongoshell stored procedure (with security risks after enabling)

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 a 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 data files:

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

Clear table data

truncate table db_mgr.dbo.T_Student

Use the Bulk Insert statement to import data files in batches:

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

Example of using OPENROWSET (BULK:

The 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 does 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