SQL 2008 TVP資料匯入之火力展示
11 | 27,504 |
切入主題前,先感謝網友小言在留言中分享了SQL 2008 TVP(Table Value Parameter,資料表值參數),一個我差點錯過的好物。
過去遇到要從程式端塞大量資料到資料庫的場合,我慣用的伎倆手法不外乎產生INSERT Script與BULK INSERT這兩招,坦白說都挺麻煩的,逃不掉將記憶體中DataTable物件轉換為Script或CSV Text的過程,而既然有 Object -> Text的需求,就免不了得針對不同型別寫邏輯。如果能直接把DataTable轉成資料庫端的Table,免除中間額外轉換,肯定能省下可觀的Coding及執行時間。
SQL Server 2008新增的資料表值參數就是針對這樣的需求所生的,若用黑手必懂的淺顯術話來解釋,就是"執行SqlCommand時,可用SqlParameter將整個ADO.NET DataTable抛到SQL 2008,然後可在SQL端直接SELECT * FROM剛才傳過去的內容",很夢幻吧?
我寫了一個簡單範例,仿照SQL 2008上資料表的結構,用C#建一個Schema相同的DataTable,並塞入2,000筆資料,接著分別用 1) SqlCommand跑2,000迴圈設參數執行INSERT INTO,以及 2) 使用TVP將整個DataTable傳過去再INSERT INTO ... SELECT * FROM…。透過Stopwatch計時,比較兩種做法的效能差異:
(要使用TVP傳送.NET DataTable,在Server端必須先針對DataTable結構宣告對應的User-Defined Table Type(使用者定義資料表類型),宣告方法可參見程式碼中CreateTableAndTableType()方法內的T-SQL,而在本範例中,Player資料表、TVP_Player Table Type)與C#的DataTable定義設成一致,以簡化資料搬移邏輯)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
namespace TestTVP
{
class Program
{
private static string cnStr =
"Data Source=Sql2008Server;User Id=user; Password=pwd;Initial Catalog=Playground";
static void Main(string[] args)
{
int TEST_SIZE = 2000;
using (SqlConnection cn = new SqlConnection(cnStr))
{
Stopwatch sw = new Stopwatch();
cn.Open();
//建立測試要用的Table及Table Type
sw.Start();
CreateTableAndTableType(cn);
sw.Stop();
Console.WriteLine("建立測試環境 {0:N0}ms", sw.ElapsedMilliseconds);
//建立DataTable物件並塞入測試資料
sw.Reset();
sw.Start();
DataTable t = new DataTable();
t.Columns.Add("Id", typeof(Guid));
t.Columns.Add("Name", typeof (string));
t.Columns.Add("Birthday", typeof (DateTime));
t.Columns.Add("Score", typeof (int));
Random rnd = new Random();
for (var i = 0; i < TEST_SIZE; i++)
t.Rows.Add(
Guid.NewGuid(),
string.Format("User{0}", i),
DateTime.Today.AddDays(rnd.Next(20000)),
rnd.Next(65536)
);
sw.Stop();
Console.WriteLine("建立資料 {0:N0}ms", sw.ElapsedMilliseconds);
//測試一: 使用SqlCommand連續INSERT
sw.Reset();
sw.Start();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText =
@"
INSERT INTO [Playground].[dbo].[Player]
([Id],[Name],[BirthDay],[Score])
VALUES
(@Id, @Name, @BirthDay, @Score)
";
SqlParameter pId =
cmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier);
SqlParameter pName =
cmd.Parameters.Add("@Name", SqlDbType.VarChar);
SqlParameter pBirthDay =
cmd.Parameters.Add("@BirthDay", SqlDbType.DateTime);
SqlParameter pScore =
cmd.Parameters.Add("@Score", SqlDbType.Int);
foreach (DataRow row in t.Rows)
{
pId.Value = row["Id"];
pName.Value = row["Name"];
pBirthDay.Value = row["BirthDay"];
pScore.Value = row["Score"];
cmd.ExecuteNonQuery();
}
sw.Stop();
Console.WriteLine("SqlCommand連環Call {0:N0}ms", sw.ElapsedMilliseconds);
sw.Reset();
sw.Start();
TruncateTable(cn);
sw.Stop();
Console.WriteLine("清除資料表 {0:N0}ms", sw.ElapsedMilliseconds);
//測試2 使用TVP
sw.Reset();
sw.Start();
cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO Player SELECT * FROM @Player";
SqlParameter pTVP = cmd.Parameters.Add("@Player", SqlDbType.Structured);
pTVP.Value = t; //SqlParameter選用SqlDbType.Structured並指定TypeName
pTVP.TypeName = "TVP_Player";
cmd.ExecuteNonQuery();
sw.Stop();
Console.WriteLine("TVP匯入資料表 {0:N0}ms", sw.ElapsedMilliseconds);
cn.Close();
}
Console.Read();
}
static void CreateTableAndTableType(SqlConnection cn)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText =
@"
IF EXISTS (
SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('Player')
)
BEGIN
DROP TABLE Player
DROP TYPE TVP_Player
END;
CREATE TABLE [Player] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [varchar](32) NOT NULL,
[BirthDay] [datetime] NOT NULL,
[Score] [int] NOT NULL,
CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TYPE dbo.TVP_Player AS TABLE (
[Id] [uniqueidentifier] NOT NULL,
[Name] [varchar](32) NOT NULL,
[BirthDay] [datetime] NOT NULL,
[Score] [int] NOT NULL,
PRIMARY KEY ( Id )
);
";
cmd.ExecuteNonQuery();
}
static void TruncateTable(SqlConnection cn)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "TRUNCATE TABLE Player";
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
}
}
測試結果如下:
建立測試環境 37ms
建立資料 11ms
SqlCommand連環Call 19,292ms
清除資料表 11ms
TVP匯入資料表 64ms
不多不多,19,292ms vs 65ms,只快了300倍而已,這下子大家都知道該怎麼做了吧?
Comments
# by dino
長見識了,感謝!
# by Alan
這實在是精采 19,292ms vs 65ms,只快了300倍而已 又多了一個昇級到MS SQL 2008的理由了
# by Daniel
既然是使用 MS-SQL 那麼如果 CommandType 改用TableDirect 來 Insert 大量資料,這樣是會比較快,還是比較慢?
# by Jeffrey
to Daniel,TableDirect只能應用在OLE DB(http://bit.ly/meCe7U),爬文所能找到的範例幾乎都集中在SQL CE,拿來與SQL Server整合即便可行,恐怕也是條人跡罕至的偏僻小徑,依我的直覺應會避用在正式用途,以免撞鬼時求救無門 :D
# by Auden
請問sqlce可以使用tvp嗎?
# by Jeffrey
to Auden, 我沒有找到SQLCE支援TVP的文件,猜想是不行。
# by Debby
請問有沒有 for oracle 的範例呢 (.net framework 3.5) 是不是可以用Oracle.DataAccess(ODP.NET)? 謝謝
# by Jeffrey
to Debby, Managed ODP.NET Array Binding 範例 https://blog.darkthread.net/blog/managed-odpnet-array-binding/ ODP.NET OracleBulkCopy 範例 https://blog.darkthread.net/blog/odp-net-oraclebulkcopy/
# by Debby
謝謝黑大...這麼快速的回應...感恩....
# by Debby
sorry, 我剛才的發問太簡略了, 我其實是想要傳 table 參數到oracle package procedure, 看到黑大分享SQL TVP 用SqlDbType.Structured 傳參數, 想說不知有沒有傳oracle的範例 (.net framework 3.5), CREATE OR REPLACE PACKAGE My_PKG AS type my_rec is record (id detail.id%type, name detail.name%type ); type my_detail_tab is table of my_rec; PROCEDURE my_proc(p_detail_list My_PKG.my_detail_tab); END My_PKG; 謝謝
# by Jeffrey
to Debby, 原來還有這招,我沒用過,但有查到一篇文章 http://appsjack.blogspot.com/2010/09/pass-custom-udt-types-to-oracle-stored.html 是這個嗎?