海量数据如何快速批量导入数据库

Pc498471249 2013-03-27 06:17:30

如题，导入之前还需要判断数据是否重复，如果存在重复，则累加它的属性值 ,数据表根据条件分了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