針對Sqlserver年夜數據量拔出速度慢或喪失數據的處理辦法

針對Sqlserver年夜數據量拔出速度慢或喪失數據的處理辦法。本站提示廣大學習愛好者：（針對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