剛進(jìn)公司時(shí),領(lǐng)導(dǎo)說(shuō)物流水系統(tǒng)有一個(gè)問(wèn)題:發(fā)送水票速度很慢,每發(fā)送一次就得等10多分鐘,問(wèn)我有沒(méi)有解決方法,時(shí)間原因一直沒(méi)去研究。
今天早上reader 上收到cnblogs的訂閱里看到一個(gè)關(guān)于SQL語(yǔ)句快速插入的文章,提到SqlBulkCopy,感覺(jué)不錯(cuò),按他的測(cè)試SqlBulkCopy要比普通插入快近30倍,
按這個(gè)來(lái)算,我們那個(gè)發(fā)水票的時(shí)間就會(huì)由 10分鐘-->20秒,這可太神奇了。
于是乎,下demo,測(cè)試,改成自己一般使用的方法測(cè)試,NND,還真可以說(shuō)是極速。
在此貼上我的Demo:UploadFiles//SqlBulkCopy.rar
using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
namespace ConsoleAppInsertTest
{
class Program
{
static int count = 1000000; //插入的條數(shù)
static void Main(string[] args)
{
long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}條數(shù)據(jù)所用的時(shí)間是{0}毫秒", sqlBulkCopyInsertRunTime, count));
long commonInsertRunTime = CommonInsert();
Console.WriteLine(string.Format("普通方式插入{1}條數(shù)據(jù)所用的時(shí)間是{0}毫秒", commonInsertRunTime, count));
Console.ReadKey();
}
/// <summary>
/// 使用普通插入數(shù)據(jù)
/// </summary>
/// <returns></returns>
private static long CommonInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
for (int i = 0; i < count; i++)
{
SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");
}
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
/// <summary>
/// 使用SqlBulkCopy方式插入數(shù)據(jù)
/// </summary>
/// <returns></returns>
private static long SqlBulkCopyInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DataTable dataTable = GetTableSchema();
for (int i = 0; i < count; i++)
{
DataRow dataRow = dataTable.NewRow();
dataRow[2] = Guid.NewGuid();
dataTable.Rows.Add(dataRow);
}
//Console.WriteLine(stopwatch.ElapsedMilliseconds);//初始化數(shù)據(jù)時(shí)間
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);
sqlBulkCopy.DestinationTableName = "Passport";
if (dataTable != null && dataTable.Rows.Count != 0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
private static DataTable GetTableSchema()
{
return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];
}
}
}
原文:http://www.yongfa365.com/Item/SqlBulkCopy.html