程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 針對Sqlserver大數據量插入速度慢或丟失數據的解決方法

針對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 字段中的超詭異字符解決過程

文章作者：jhkdiy 發表日期：2009-10-11 程

SQL server 2005中設置自動編號字段的方法，sqlserver

SQL server 2005中設置自動編號字段的方法，sq

不固定參數的存儲過程實現代碼

我想此時不妨使用字符串參數來幫助我們解決這種情況，利用字符

SQL Server時間函數用法詳解

SQL中的時間函數非常有用，特別是在我們進行初始賦值、復雜查

SQL2005Express中導入ACCESS數據庫的兩種方法，access數據庫導入

SQL2005Express中導入ACCESS數據庫的兩種方

利用SQL Server數據庫郵件服務實現監控和預警，sqlserver

利用SQL Server數據庫郵件服務實現監控和預警，sql

熱門圖文

MySQL中的SUM函數使用教程，mysqlsum使用教程刪除數據庫字段中的漢字或字符 Sql Server 2000刪除數據庫備份文件 SQL Server數據庫查詢優化3種技巧 SQL Server與Oracle數據庫在安全性上的異同（1）實例分析Sybase數據庫Varchar類型的排序數據庫大戰： MS SQL Server & IBM DB2 DB2 優化器中針對JOIN 結果集估計經典版！

欄目導航

PostgreSQL 更多數據庫知識 PL/SQL