切入主題前,先感謝網友小言在留言中分享了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 是這個嗎?

Post a comment