400多万条数据插入sqlexpress2008使用sqlbulkcopy如何提高速度？

[fannychen90]

我是新手，vs2008读二进制文件然后插入sql，使用sqlbulkcopy花费近20分钟，请帮看下如何提高速度2分钟以内。代码以下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace Forms
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
CreateViewItemMethodOne();

}


private void CreateViewItemMethodOne()
{
//读二进制文件数据入list
System.Collections.Specialized.StringCollection result = GetAllFiles(@"C:\jcb_sina\vipdoc\");
List<StockDayData> list = new List<StockDayData>();
for (int i = 0; i < result.Count; i++)
{
if (result[i].Substring(result[i].Length - 3) == "day")
//MessageBox.Show(result[i]);
{

string filename = result[i];
string Stockname = filename.Substring(0, filename.Length - 4);
Stockname = Stockname.Substring(Stockname.Length - 8);
FileStream fs = File.OpenRead(result[i]);
BinaryReader br = new BinaryReader(fs);
int days = (int)fs.Length / 32;
//List<StockDayData> list = new List<StockDayData>();
for (int j = 0; j < days; j++)
{
StockDayData item = new StockDayData();
//item.DataDate =DateTime.Parse(new string(br.ReadChars(8)));
int date = br.ReadInt32();
int year = date / 10000;
int month = int.Parse(date.ToString().Substring(4, 2));
int day = int.Parse(date.ToString().Substring(6, 2));
//item.DataDate = new DateTime(year, month, day);

int open = br.ReadInt32();
int high = br.ReadInt32();
int low = br.ReadInt32();
int close = br.ReadInt32();
Single amount = br.ReadSingle();
//Int32 amount = br.ReadInt32();
decimal am = Convert.ToDecimal(amount);
long amstr = Convert.ToInt64(amount);
int vol = br.ReadInt32();
int preclose = br.ReadInt32();

//Decimal open = Convert.ToDecimal(br.ReadSingle());
item.Stockname = Stockname;
item.Date = date;
item.Open = Convert.ToDecimal(open / 100m);
item.High = Convert.ToDecimal(high / 100m);
item.Low = Convert.ToDecimal(low / 100m);
item.Close = Convert.ToDecimal(close / 100m);
item.Amount = amstr;
item.Vol = vol;
item.PreClose = Convert.ToDecimal(preclose / 100m);


list.Add(item);
}
br.Close();
fs.Close();
this.dataGridView1.DataSource = list;//dataGridView显示数据快2分钟
}
}
Inert2DBBySqlBulkCopy_TB_StockDayInfo(list);//插入sql，这里是不是有问题？
}

public static void Inert2DBBySqlBulkCopy_TB_StockDayInfo(List<StockDayData> list)//插入sql方法
{

DataTable dt = new DataTable();
dt.Columns.Add("Stockname", System.Type.GetType("System.String"));
dt.Columns.Add("Date", System.Type.GetType("System.Int32"));
dt.Columns.Add("Open", System.Type.GetType("System.Decimal"));
dt.Columns.Add("High", System.Type.GetType("System.Decimal"));
dt.Columns.Add("Low", System.Type.GetType("System.Decimal"));
dt.Columns.Add("Close", System.Type.GetType("System.Decimal"));
dt.Columns.Add("Vol", System.Type.GetType("System.Decimal"));
dt.Columns.Add("Amount", System.Type.GetType("System.Decimal"));
dt.Columns.Add("PreClose", System.Type.GetType("System.Decimal"));

foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["Stockname"] = item.Stockname;
dr["Date"] = item.Date.ToString();
dr["Open"] = item.Open;
dr["High"] = item.High;
dr["Low"] = item.Low;
dr["Close"] = item.Close;
dr["Vol"] = item.Vol;
dr["Amount"] = item.Amount;
dr["PreClose"] = item.PreClose;
dt.Rows.Add(dr);
}
SqlConnection conn = new SqlConnection();
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = ".\\SQLExpress";
builder.InitialCatalog = "StockData";
builder.IntegratedSecurity = true;
conn.ConnectionString = builder.ConnectionString;
conn.Open();

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))//这里是不是需要多线程，如何改代码？
{
sqlBulkCopy.ColumnMappings.Add(0, "Stockname");
sqlBulkCopy.ColumnMappings.Add(1, "Date");
sqlBulkCopy.ColumnMappings.Add(2, "Open");
sqlBulkCopy.ColumnMappings.Add(3, "High");
sqlBulkCopy.ColumnMappings.Add(4, "Low");
sqlBulkCopy.ColumnMappings.Add(5, "Close");
sqlBulkCopy.ColumnMappings.Add(6, "Vol");
sqlBulkCopy.ColumnMappings.Add(7, "Amount");
sqlBulkCopy.ColumnMappings.Add(8, "PreClose");

sqlBulkCopy.BatchSize = 100000;
sqlBulkCopy.BulkCopyTimeout = 60;
sqlBulkCopy.DestinationTableName = "TB_StockDayInfo";
sqlBulkCopy.WriteToServer(dt);
}


}
catch (SqlException e)
{
Console.WriteLine("Error accessing the database: {0}", e.Message);
}
finally
{
conn.Close();
}

}

public class StockDayData//数据struct
{
private string mStockname;

public string Stockname
{
get { return mStockname; }
set { mStockname = value; }
}

private int mDate;

public int Date
{
get { return mDate; }
set { mDate = value; }
}

private decimal mOpen;

public decimal Open
{
get { return mOpen; }
set { mOpen = value; }
}

private decimal mHigh;

public decimal High
{
get { return mHigh; }
set { mHigh = value; }
}

private decimal mLow;

public decimal Low
{
get { return mLow; }
set { mLow = value; }
}

private decimal mClose;

public decimal Close
{
get { return mClose; }
set { mClose = value; }
}

private long mAmount;

public long Amount
{
get { return mAmount; }
set { mAmount = value; }
}

private int mVol;

public int Vol
{
get { return mVol; }
set { mVol = value; }
}

private decimal mPreClose;

public decimal PreClose
{
get { return mPreClose; }
set { mPreClose = value; }
}

}

public void GetAllFiles(string parentDir, System.Collections.Specialized.StringCollection result)
{
string[] dir = System.IO.Directory.GetDirectories(parentDir);
for (int i = 0; i < dir.Length; i++)
GetAllFiles(dir[i], result);
string[] file = System.IO.Directory.GetFiles(parentDir);
for (int i = 0; i < file.Length; i++)
result.Add(file[i]);
}

public System.Collections.Specialized.StringCollection GetAllFiles(string rootdir)
{
System.Collections.Specialized.StringCollection result = new System.Collections.Specialized.StringCollection();
GetAllFiles(rootdir, result);
return result;
}


}
}

[缪军]

1.SqlServer2008支持表值参数,和SqlBulkCopy区别参见MSDN(其中有个详细的对照表):
http://msdn.microsoft.com/zh-cn/library/bb510489(v=SQL.100).aspx
2.400万的数据就是在网络上传递也需要不少时间,这里给出几个建议:
a.压缩包上传到服务器,再导入,要说明的是,这些功能最好编程实现,而不是通过后台人工介入,但同时,怎样保护服务器的上传目录,对很多程序员是个难题;
b.按100份或者1000份为单位,分割数据,按块导入,最好不要多线程,这样的话不仅可以给客户显示进度,还可以报告和处理异常,延迟处理不能导入的数据
c.可以由数据库代理导入,表值参数,SqlBulkCopy三种方案,但是在SqlServer2008的技术条件下,一般不采用SqlBulkCopy

[缪军]

我看了下楼主的代码,貌似就是从文件获取数据,
可以将文件压缩打包,打的话分割成若干份,上传的服务器,
然后执行数据库导入代理导入到一个临时表,再做业务规则的处理

顺便再次强调一下,无论是否使用SqlBulkCopy,避免一次性导入海量的数据

[DSIOF3KIDSKTR]

不用想了，bcp是最快的，而且你是二进制文件，文件大的话当然慢，如果是一般数据的话，400w数据本机导入应该不超过60s

[yyz985]

试试一万条一插入，再差很多就要升级硬盘了

[jianshao810]

bcp 简单又快，我觉得你一次插入400w也不简单啦，说明你的服务器很了不起啦。

[DSIOF3KIDSKTR]

[Quote=引用 5 楼 microtry 的回复:]

引用 3 楼 athwind 的回复:
不用想了，bcp是最快的，而且你是二进制文件，文件大的话当然慢，如果是一般数据的话，400w数据本机导入应该不超过60s

呵呵,微软自己都说bcp不是最快的,你的结论根据什么?
[/Quote]
微软可以这么说，因为这些都是他们做的，他们让谁快谁就快，我们使用者呢？你用到的什么比bcp快？

[EnForGrass]

楼主可以打包或者分分批插入

[fannychen90]

mysql呢？没用过，速度如何，大家用过内存数据库没？速度如何

[缪军]

[Quote=引用 3 楼 athwind 的回复:]
不用想了，bcp是最快的，而且你是二进制文件，文件大的话当然慢，如果是一般数据的话，400w数据本机导入应该不超过60s
[/Quote]
呵呵,微软自己都说bcp不是最快的,你的结论根据什么?

[fannychen90]

谢谢帮助，还是没有得到想要的快速，再Google上查查资料。结贴。。。

[ijwsoft]

sql2008的 SSIS 不慢

http://blog.csdn.net/keenweiwei/article/details/6869158

[缪军]

[Quote=引用 6 楼 athwind 的回复:]
微软可以这么说，因为这些都是他们做的，他们让谁快谁就快，我们使用者呢？你用到的什么比bcp快？
[/Quote]
我一楼已经给了官方链接,自己去看