針對Sqlserver大數據量插入速度慢或丟失數據的解決方法

2020-07-25 12:58:05

字體：大中小

來源：轉載

供稿：網友

我的設備上每秒將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

上一篇：SQL Server 壓縮日志與減少SQL Server 文件大小的方法

下一篇：ms sql server中實現的unix時間戳函數（含生成和格式化，可以和mysql兼容）

學習交流

選擇筆記本處理器（CPU）需避免那些誤區和基

選擇筆記本處理器（CPU）需避免那些誤區和基本常識...

熱門圖片

猜你喜歡的新聞

猜你喜歡的關注

新聞熱點

中國移動6月凈增5G套餐用戶1459萬戶累計達7019.9萬戶

2020-07-20 18:48:56

螞蟻集團計劃在科創板和港交所同步上市估值超2000億美元

2020-07-20 18:46:41

傳滴滴最快年內在香港IPO 目標估值800億美元

2020-07-20 18:43:20

小鵬汽車宣布C+輪融資近5億美元高瓴紅杉等為主要投資者

2020-07-20 18:35:48

微博內測熱搜定制功能你可以愉快地吃瓜了嗎？

2020-07-17 23:29:31

Twitter證實：黑客一共瞄準130個名人賬號

2020-07-17 23:24:56

疑難解答

圖片精選

網友關注

国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

針對Sqlserver大數據量插入速度慢或丟失數據的解決方法