高速でINSERTしたい

お世話になります。
現在、Visual Studio 2008とSQL Serverを使用して開発をしております。
今回はCSVファイルから取り込んだデータを仮テーブル(以降テーブルA)にデータを取り込み、
その後、本テーブル(以降テーブルB)へ移行させるプログラムを作成しております。
データを移行させる際、データを加工したり、別テーブルから値を取得したりした上で
INSERTを実施しておるのでものすごく時間を要しております。
現在、約10万件のデータを移行するのに1時間ほどかかってしまっております。
今後、更に大量のデータを移行することになるととんでもない時間がかかってしまうのは明らかです。
何か良い方法はありませんでしょうか。
よろしくお願いいたします。

尚、以下のコードですが、INSERTする[テーブルB]は本来は他にまだいくつかフィールドを持っておりますが
ご説明すると長くなるのでカットさせて頂いていますが、やっていることは以下に書かせて頂きましたような
変換を行っております。

VB.NET
1
2Dim mstrSQL As String
3Dim dstTest As DataSet
4Dim dstCustSer As DataSet
5Dim intCustSer As Integer
6Dim strDate As String
7Dim strName As String
8       
9        mstrSQL = ""
10        mstrSQL = "SELECT * FROM [テーブルA]" & vbCrLf
11        mstrSQL &= " LEFT JOIN [テーブルC] ON [テーブルA].ID = [テーブルC].ID" & vbCrLf
12       
13        dstTest = (ここでデータセット取得)
14
15        If dstTest.Tables(0).Rows.Count > 0 Then
16            'シリアル取得
17            For i = 0 To dstTest.Tables(0).Rows.Count - 1
18
19                mstrSQL = ""
20                mstrSQL = "SELECT * FROM [テーブルD]" & vbCrLf
21                mstrSQL &= " WHERE ID= " & dstTest.Tables(0).Rows(i)("ID") & vbCrLf
22
23                dstCustSer = (ここでデータセット取得)
24
25                If dstCustSer.Tables(0).Rows.Count > 0 Then
26                    intCustSer = dstCustSer.Tables(0).Rows(0)("CustSer")
27                Else
28                    intCustSer = 0
29                End If
30
31              '日付
32                strDate = ""
33                If IsNothing(dstTest.Tables(0).Rows(i)("Date")) = False Then
34                    If IsDBNull(dstTest.Tables(0).Rows(i)("Date")) = False Then
35                        strDate = Microsoft.VisualBasic.Left(dstTest.Tables(0).Rows(i)("Date"), 4) & "年" _
36                                   & Microsoft.VisualBasic.Mid(dstTest.Tables(0).Rows(i)("Date"), 6, 2) & "月" _
37                                   & Microsoft.VisualBasic.Mid(dstTest.Tables(0).Rows(i)("Date"), 9, 2) & "日"
38                    End If
39                Else
40                    strDate = ""
41                End If
42
43              '名前
44                strName = ""
45                If IsNothing(dstTest.Tables(0).Rows(i)("Name")) = False Then
46                    If IsDBNull(dstTest.Tables(0).Rows(i)("Name")) = False Then
47                        strName = dstTest.Tables(0).Rows(i)("Name")
48                    Else
49                        strName = ""
50                    End If
51                Else
52                    strName = ""
53                End If
54               
55                mstrSQL = ""
56                mstrSQL = "INSERT INTO [テーブルB]" & vbCrLf
57                mstrSQL &= " (" & vbCrLf
58                mstrSQL &= "CustSerial" & vbCrLf 
59                mstrSQL &= ", Date" & vbCrLf
60                mstrSQL &= ", Name" & vbCrLf
61                mstrSQL &= ")" & vbCrLf
62                mstrSQL &= " VALUES" & vbCrLf
63                mstrSQL &= " (" & vbCrLf
64                mstrSQL &= intCustSer & "," & vbCrLf
65                mstrSQL &= "'" & strDate & "'," & vbCrLf
66                mstrSQL &= "'" & strName & "'," & vbCrLf
67                mstrSQL &= ")" & vbCrLf
68                                
69            Next
70
71        End If

YAmaGNZ

2020/03/26 06:04

1時間という処理時間は CSVを読み込みテーブルAに追加～テーブルAからテーブルBへの変換までの時間なのでしょうか？それともソースにあるテーブルAからテーブルBへの変換のみの時間なのでしょうか？とりあえず、どの処理に時間がかかっているのか計測してはどうでしょうか？また、書かれた質問からテーブルAを作る必然性が読み取れないので、CSVを読み込んでそのままテーブルBに入れるように加工すればいいのでは？とも思ってしまいます。

matsutake

2020/03/26 06:13

お世話になります。ご返信、ありがとうございます。時間はテーブルAからテーブルBへの変換までの時間です。また無駄なプログラムに対してのご指摘、ありがとうございます。もっとよく考えて作成するように致します。

行動規範の内容に同意します

回答4件

ベストアンサー

表をSELECTして別の表へINSERTする（INSERT ～ SELECT）を使っては？

投稿2020/03/26 05:52

Orlofsky

総合スコア16417

matsutake

2020/03/26 06:10

お世話になります。早々のご回答、ありがとうございます。申し訳ございませんが、具体的にはどのようにすればよいのでしょうか。

sazi

2020/03/26 06:24

質問の > (ここでデータセット取得) という所が、insert ～ select ～のselect ～の部分にあたります。質問では伏せられているので、具体的な回答はできません。

Orlofsky

2020/03/26 06:56

saziさん、フォローありがとうございます。新型コロナウイルス絡みで予定していた仕事がキャンセルになり、客先常駐案件しか見つかりません。いつ通勤途中や職場でウイルスに感染してクラスターやオーバーシュートになるかわかりません。

matsutake

2020/03/26 10:32

お世話になります。度々のご回答、ありがとうございます。今一度、皆様のご回答を参考に調べてみます。

Orlofsky

2020/03/26 10:47

INSERT SELECT は1回実行するだけで10万件のデータを処理してくれます。LOOPはしません。SQLを直接実行すれば、たぶんVB.net も不要でしょう。処理時間は数分でしょう。 SQL 入門レベルの勉強をきちんとしないと無用な時間をかけてパフォーマンスの悪いプログラムを作り続けることになります。

行動規範の内容に同意します

約10万件のデータを移行するのに1時間ほどかかってしまっております

10万件のループ内で Select文とInsert文を発行しているので20万回のSQLを発行することになります。

全部1つのSQL文で組むことができれば1回のSQL発行であとは全部SQLサーバーで処理できます。
こんな↓感じのイメージでやればいいと思います。
（T-SQL何年かぶりなので間違っていて動かない可能性大ですが・・・）

select文で[テーブルB]へInsertする形を作り上げられれば、それをvaluesに指定するだけです。

sql
1insert into [テーブルB] (CustSerial,Date,Name)
2SELECT 
3    IsNull((SELECT TOP 1 [テーブルA].CustSer FROM [テーブルD] WHERE [テーブルD].ID=[テーブルA].ID), 0) AS CustSer,
4    IsNull(FORMAT([テーブルA].date,'yyyy年MM月dd日'),'') as Date, 
5    IsNull([テーブルA].name, '') as Name
6FROM [テーブルA] 
7    LEFT JOIN [テーブルC] ON [テーブルA].ID = [テーブルC].ID

投稿2020/03/26 07:31

編集2020/03/26 09:04

Y.H.

総合スコア7918

sazi

2020/03/26 09:01

insertでselectを使用する場合はvaluesは使用しません（というか文法エラーです）

Y.H.

2020/03/26 09:05

指摘ありがとうございます。やはり間違ってましたね。。。

matsutake

2020/03/26 10:18

お世話になります。ご回答、ありがとうございます。皆様のいろいろなご意見をお聞きすることができ感謝しております。引き続き、調べてみます。

行動規範の内容に同意します

ループ処理をする過程でDataTableに加工後のデータを入れる処理を行い(ループ中でINSERTは行わない)
最終的にそのDataTableを一括挿入してはいかかでしょうか？

SqlBulkCopy.WriteToServer メソッド

投稿2020/03/26 09:33

編集2020/03/26 09:38

polupolu_0218

総合スコア65

matsutake

2020/03/26 10:17

お世話になります。ご回答、ありがとうございます。ご確認させていただきます。

行動規範の内容に同意します

VB.NET のコードで & で文字列をつなぎ合わせて INSERT クエリの文字列を組み立てているようですが、それでは文字列の組み立てに時間がかかるし、それより何より毎回がアドホッククエリになるので、遅くても当たり前と思うのですが。

まずはそこをパラメータ化クエリを使うことで改善してはいかがですか？

投稿2020/03/26 06:04

編集2020/03/26 06:14

退会済みユーザー

総合スコア0

matsutake

2020/03/26 06:14

お世話になります。早々のご回答、ありがとうございます。パラメータ化クエリについて調べてみます。

退会済みユーザー

2020/03/26 06:28

ぱっと見明らかに問題ありそうな部分をコメントしましたが、そこがボトルネックになっているのかどうかまでは分かりません。遅いということへの対策は、どこがボトルネックになっているかを見極めて、そこを改善しないと効果が薄いかもしれません。・・・が、パラメータ化クエリを使うように書き換えるのはそれほど手間のかかることではないし、そもそもパラメータ化してないということはプログラマとして有り得ないレベルの話ですので、これを機会に改善されてはいかがですか？

sazi

2020/03/26 06:32

質問のロジックで性能的に問題なのは、SQLの発行回数が多い事です。パラメータクエリーで行っても、根本的な解決にはなりません。

退会済みユーザー

2020/03/26 06:58 編集

> パラメータクエリーで行っても、根本的な解決にはなりません。質問のコードでは毎回アドホッククエリを投げて毎回コンパイルすると思うのですが、そうであればコンパイルが一回で済み実行プランが再利用できるパラメータ化クエリを使う方が明らかによさそうだと思います。違うのでしょうか？ただ、上に書いたように、そこが全体で一番細いボトルネックになっているのかどうかまでは分かりませんが。ラウンドトリップを減らす方が（CSV ファイルからバルクインサートという手段を使うとかの方法の方が）効果があるかもしれませんね。

sazi

2020/03/26 07:10

事前コンパイルの性能への貢献は微々たるものです。今回の、insert into values 構文の場合は、キャッシュ効果も望めません。この手のものはSQLの発行回数を如何に減らすかが鍵なのです。

退会済みユーザー

2020/03/26 07:13

では、sazi さんが効果があると思う回答をしてあげてください。

sazi

2020/03/26 07:30

質問内容が省略されているので、回答は控えています。ぶら下がりで、お気に障ってしまったなら申し訳ありません。

matsutake

2020/03/26 10:26

お世話なっております。私の実力不足、説明不足により、申し訳ございません。省略しておる箇所は、以下のように書いております。 ```VB.NET dstTest = (ここでデータセット取得) dstTest = GetDst(mstrSQL) '上記の部分はこのように書いております Private Function GetDst(Byval StrSQL As String) As DataSet Dim mDad As OleDb.OleDbDataAdapter Dim mDst As DataSet mDad = New OleDb.OleDbDataAdapter(strSQL, mstrCnn) mDst = New DataSet() mDst.Clear() mDad.Fill(mDst) Return mDst End Function ```

matsutake

2020/03/26 10:30

何度もご回答、ありがとうございます。自分の実力のなさが恥ずかしい限りでございます。質問する者として、最低限の知識や技術等を持ち合わせることはこういう場でのマナーだとは思いましたが、今回ご質問させていただきました。今回の質問に限らず、いろいろと考えさせられました。ありがとうございました。

退会済みユーザー

2020/03/26 10:48 編集

> 省略しておる箇所は、以下のように書いております。そういうのは回答欄を編集して書いてください。ここに書いても見る人はほとんどいませんので。（それは遅いということにほとんど関係なさそうですが）

行動規範の内容に同意します

あなたの回答