海量数据如何快速批量导入数据库
本帖最后由 Pc498471249 于 2013-03-27 18:19:14 编辑 如题， 导入之前还需要判断数据是否重复，如果存在重复，则累加它的属性值 ,数据表根据条件分了N张， 表名是动态的。 我原来的做法是， 写一个存储过程， C#程序里拼接 "exec 存储过程 参数,参数" 这样的字符串,没拼接到500次， 提交一次。 开始导入还算快。 半个小时100W 左右的量， 但是当数据量得到几KW之后， 速度就非常慢了 . 我把存储过程 和 C#的代码晒出来看看， 大家帮忙看看那里可以做优化啊
private void ImportDataAndInsertData_Click(object sender, RoutedEventArgs e)
{
System.Windows.Forms.OpenFileDialog ofd = new System.Windows.Forms.OpenFileDialog() { Filter = "文本文件|*.txt", Multiselect = false };
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
t = new Thread(new ThreadStart(() =>
{
dt.Start();
Dispatcher.BeginInvoke(new Action(() => { ImportDataAndInsertData.IsEnabled = false; }));
string[] datas = File.ReadAllLines(ofd.FileName,Encoding.Default); //datas.Length大概在500W-800W左右
ofd.Dispose();
string[] newdatas = RemoveDup3(datas); //去掉重复
Dispatcher.BeginInvoke(new Action(() => { txtAllCount.Text = "总行数:" + newdatas.Length; }));
string sql = string.Empty; //拼接sql语句
for (int i = 0; i < newdatas.Length; i++)
{
string[] tempstr = newdatas[i].Replace("----", "∽").Split('∽');
sql += "exec AddData @QQNumber=" + tempstr[0] + ",@QQPassword='" + tempstr[1].Replace("'", "") + "',@BeiZhu='NULL'";
if (i % 500 == 0)
{
sql = sql.Insert(0, "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; set nocount on; begin transaction begin ");
sql += " end commit";
ds.AddData(sql); //执行插入
sql = string.Empty;
}
current = i;
}
current = newdatas.Length;
sql = sql.Insert(0, "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; set nocount on; begin transaction begin ");
sql += " end commit";
ds.AddData(sql);
sql = string.Empty;
dt.Stop();
current = 0;
seconds = minuts = hours = 0;
Dispatcher.BeginInvoke(new Action(() =>
{
ImportDataAndInsertData.IsEnabled = true;
txtAllCount.Text = "总行数:0";
txtCurrentCount.Text = "当前行数:0";
}));
datas = newdatas = null;
MessageBox.Show("数据导入成功");
t.Abort();
}));
t.Start();
}
}
USE [QQData]
GO
/****** Object: StoredProcedure [dbo].[AddData] Script Date: 03/27/2013 18:01:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddData](@QQNumber bigint,@QQPassword varchar(1000),@BeiZhu varchar(1000)='NULL')
AS
BEGIN
/*清除干扰查询*/
/*DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
不返回影响行数*/
set nocount on;
declare @tableName varchar(5)=substring(Convert(varchar(12),@QQNumber),1,5);
declare @QN bigint=0; /*重复的Q号*/
declare @QP varchar(1000); /*重复的密码*/
declare @sql varchar(1000);
declare @checksql nvarchar(200); /*检查QQ号是否重复*/
set @checksql='select top 1 @QN=QQNumber,@QP=QQPassword from Tab_'+@tableName+' where QQNumber='+Convert(varchar(12),@QQNumber);
exec sp_executesql @checksql,N'@QN bigint output,@QP varchar(1000) output',@QN output,@QP output;
if(@QN!=0)
begin
/*更新（去除重复密码）*/
if(charindex(@QQPassword,@QP,0)=0)
begin
/*不包含重复密码*/
set @QQPassword='∝'+@QQPassword;
set @sql='update Tab_'+@tableName+' set QQPassword=QQPassword+'''+@QQPassword+''' where QQNumber='+Convert(varchar(12),@QQNumber);
exec(@sql);
end
end
else
begin
/*添加新数据*/
set @sql='insert into Tab_'+@tableName+'(QQNumber,QQPassword,BeiZhu) values('''+CONVERT(varchar(12),@QQNumber)+''','''+@QQPassword+''','+@BeiZhu+')';
exec(@sql);
end
END
