CSV ファイルを マルチテーブル・インサート を使ってインポートする

大量データをDBに取り込む時、Oracleではマルチテーブル・インサートという機能があります。
CSVファイルを1レコードずつ読み込んでDBにINSERT、とかよくありますよね。
現行システムがここで時間かかっていたので、対応したいと思います。

※PL/SQL or SQL*Loader の方が早いけど、今回は可能な限り修正コストを抑えたいので、コード修正だけで対応できるこの方法で。

構文はこんな感じ。

insert all
into sample_table (id, name, value) values (1, 'TKISHIMO', 0)
into sample_table2 (id, name, value) values (2, 'MHAYASHI', 0)
select * from dual;

1つのSQL文で複数の表にINSERTする絶品テクニック（1/2） − ＠IT

では、何件ずつ突っ込めば一番早いんだ？検証してみる。

using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;

namespace MultiInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            var connStr = new OracleConnectionStringBuilder();
            connStr.UserID = "UID";
            connStr.Password = "PWD";
            connStr.DataSource = "SID";

            using (var conn = new OracleConnection(connStr.ToString()))
            {
                try
                {
                    conn.Open();
                    //create
                    Execute(String.Format("create table Test({0})", GetNames(true)), conn);

                    var insert = string.Format("insert into Test({0}) values({1})", GetNames(false), GetValue());
                    var sqls = new List<string>();
                    for (int i = 0; i < 50000; i++)
                        sqls.Add(insert);

                    var sw = new Stopwatch();
                    sw.Start();
                    foreach (var sql in sqls)
                        Execute(sql, conn);
                    sw.Stop();
                    Console.WriteLine("   1件ずつinsert : {0}", sw.Elapsed);

                    sw.Reset();
                    sw.Start();
                    MultiExecute(sqls, conn, 10);
                    sw.Stop();
                    Console.WriteLine("  10件ずつinsert : {0}", sw.Elapsed);

                    sw.Reset();
                    sw.Start();
                    MultiExecute(sqls, conn, 100);
                    sw.Stop();
                    Console.WriteLine(" 100件ずつinsert : {0}", sw.Elapsed);

                    sw.Reset();
                    sw.Start();
                    MultiExecute(sqls, conn, 500);
                    sw.Stop();
                    Console.WriteLine(" 500件ずつinsert : {0}", sw.Elapsed);

                    //delete
                    Execute("drop table Test", conn);
                    Console.WriteLine("Press any key...");
                    Console.ReadLine();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

        private static void MultiExecute(List<string> sqls, OracleConnection conn, int p)
        {
            var sb = new StringBuilder();
            var array = new string[p];
            for (int i = 0; i < sqls.Count; i += p)
            {
                sqls.CopyTo(i, array, 0, p);
                sb.AppendLine("insert all");
                foreach (var item in array)
                    sb.AppendLine(item.Replace("insert ", ""));

                sb.AppendLine("select * from dual");
                Execute(sb.ToString(), conn);
                sb.Clear();
            }
        }

        static string GetNames(bool isCreate)
        {
            var list = new List<String>();
            var type = isCreate ? "VARCHAR2(50)" : "";
            for (int i = 0; i < 100; i++)
                list.Add(String.Format("V_{0:000} {1}", i, type));
            return String.Join(",", list);
        }

        static string GetValue()
        {
            var list = new List<String>();
            for (int i = 0; i < 100; i++)
                list.Add("'test string'");
            return String.Join(",", list);
        }

        static void Execute(string sql, OracleConnection conn)
        {
            using (var cmd = new OracleCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
}

結果はこんな感じ。

   1件ずつinsert : 00:01:15.7948220
  10件ずつinsert : 00:00:13.7392012
 100件ずつinsert : 00:00:06.7394553
 500件ずつinsert : 00:01:04.8971647
Press any key...

多すぎても良くないみたい。
だが、1件ずつループでINSERTするよりも断然早い。