針對(duì)Sqlserver大數(shù)據(jù)量插入速度慢或丟失數(shù)據(jù)的解決方法
我的設(shè)備上每秒將2000條數(shù)據(jù)插入數(shù)據(jù)庫,2個(gè)設(shè)備總共4000條,當(dāng)在程序里面直接用insert語句插入時(shí),兩個(gè)設(shè)備同時(shí)插入大概總共能插入約2800條左右,數(shù)據(jù)丟失約1200條左右,測(cè)試了很多方法,整理出了兩種效果比較明顯的解決辦法:
方法一:使用Sql Server函數(shù):
1.將數(shù)據(jù)組合成字串,使用函數(shù)將數(shù)據(jù)插入內(nèi)存表,后將內(nèi)存表數(shù)據(jù)復(fù)制到要插入的表。
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',每行數(shù)據(jù)中間用“;”隔開,每個(gè)字段之間用“|”隔開。
3.編寫函數(shù):
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) --申明單個(gè)接收值 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)--獲取分割符個(gè)數(shù) IF @j=0 BEGIN --INSERT INTO @t VALUES (@substr,1) --沒有分割符則插入整個(gè)字串 set @substr2=@substr; set @ii=0 SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--獲取分割符個(gè)數(shù) 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)) --去除已獲取的分割串,得到還需要繼續(xù)分割的字符串 end else BEGIN --當(dāng)循環(huán)到最后一個(gè)值時(shí)將數(shù)據(jù)插入表 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) -----二次循環(huán)開始 --1.線獲取要二次截取的字串 set @substr2=(LEFT(@substr,@m)); --2.初始化二次截取的起始位置 set @ii=0 --3.獲取分隔符個(gè)數(shù) SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--獲取分割符個(gè)數(shù) 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)) --去除已獲取的分割串,得到還需要繼續(xù)分割的字符串 end else BEGIN --當(dāng)循環(huán)到最后一個(gè)值時(shí)將數(shù)據(jù)插入表 INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) END --END SET @ii=@ii+1 END -----二次循環(huán)結(jié)束 SET @substr=RIGHT(@substr,LEN(@substr)-(@m+1)) --去除已獲取的分割串,得到還需要繼續(xù)分割的字符串 END ELSE BEGIN --INSERT INTO @t VALUES(@substr,@i+1)--對(duì)最后一個(gè)被分割的串進(jìn)行單獨(dú)處理 -----二次循環(huán)開始 --1.線獲取要二次截取的字串 set @substr2=@substr; --2.初始化二次截取的起始位置 set @ii=0 --3.獲取分隔符個(gè)數(shù) SET @jj=LEN(REPLACE(@substr2,@splitchar2,REPLICATE(@splitchar2,2)))-LEN(@substr2)--獲取分割符個(gè)數(shù) 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)) --去除已獲取的分割串,得到還需要繼續(xù)分割的字符串 end else BEGIN --當(dāng)循環(huán)到最后一個(gè)值時(shí)將數(shù)據(jù)插入表 INSERT INTO @t VALUES(@MaxValue,@Phase,@SlopeValue,''+@Data+'',@Alarm,@AlmLev,cast(@substr2 as datetime),GETDATE()) END SET @ii=@ii+1 END -----二次循環(huán)結(jié)束 END SET @i=@i+1 END END RETURN END
4.調(diào)用函數(shù)語句:
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.結(jié)果展示:
select * from [mytable] ;
方法二:使用BULK INSERT
大數(shù)據(jù)量插入第一種操作,使用Bulk將文件數(shù)據(jù)插入數(shù)據(jù)庫
Sql代碼
創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE [db_mgr] GO
創(chuàng)建測(cè)試表
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
填充測(cè)試數(shù)據(jù)
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存儲(chǔ)過程(開啟后有安全隱患)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1; EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
使用bcp導(dǎo)出格式文件:
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'
使用bcp導(dǎo)出數(shù)據(jù)文件:
EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T'
將表中數(shù)據(jù)清空
truncate table db_mgr.dbo.T_Student
使用Bulk Insert語句批量導(dǎo)入數(shù)據(jù)文件:
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
相關(guān)文章
一次性壓縮Sqlserver2005中所有庫日志的存儲(chǔ)過程
通過下面這個(gè)可以執(zhí)行單個(gè)數(shù)據(jù)庫日志壓縮,問題每次都要寫數(shù)據(jù)庫名字,日志大的話執(zhí)行速度沒有那么快2012-01-01SQL?Server中T-SQL標(biāo)識(shí)符介紹與無排序生成序號(hào)的方法
這篇文章介紹了SQL?Server中T-SQL標(biāo)識(shí)符與無排序生成序號(hào)的方法,文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-05-05uniqueidentifier轉(zhuǎn)換成varchar數(shù)據(jù)類型的sql語句
uniqueidentifier轉(zhuǎn)換成varchar數(shù)據(jù)類型的sql語句,需要的朋友可以參考下。2011-09-09SQL Server 2014 數(shù)據(jù)庫中文版安裝圖文教程
這篇文章主要介紹了SQL Server 2014 數(shù)據(jù)庫中文版安裝圖文教程,需要的朋友可以參考下2021-05-05SqlServer提示“列前綴tempdb.無效: 未指定表名”問題解決方案
這篇文章主要介紹了SqlServer提示“列前綴tempdb.無效: 未指定表名”問題解決方案,需要的朋友可以參考下2014-08-08SQL Server利用sp_spaceused如何查看表記錄存在不準(zhǔn)確的情況
這篇文章主要給大家介紹了關(guān)于SQL Server利用sp_spaceused如何查看表記錄存在不準(zhǔn)確情況的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用SQL Server具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04Access 數(shù)據(jù)類型與 MS SQL 數(shù)據(jù)類型的相應(yīng)
Access 數(shù)據(jù)類型與 MS SQL 數(shù)據(jù)類型的相應(yīng)...2006-10-10