質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

VB.NET

Microsoft Visual Basic .NETのことで、Microsoft Visual Basic(VB6)の後継。 .NET環境向けのプログラムを開発することができます。 現在のVB.NETでは、.NET Frameworkを利用して開発を行うことが可能です。

Q&A

解決済

4回答

6907閲覧

高速でINSERTしたい

matsutake

総合スコア34

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

VB.NET

Microsoft Visual Basic .NETのことで、Microsoft Visual Basic(VB6)の後継。 .NET環境向けのプログラムを開発することができます。 現在のVB.NETでは、.NET Frameworkを利用して開発を行うことが可能です。

0グッド

1クリップ

投稿2020/03/26 05:49

お世話になります。
現在、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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

YAmaGNZ

2020/03/26 06:04

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

2020/03/26 06:13

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

回答4

0

ベストアンサー

投稿2020/03/26 05:52

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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 入門レベルの勉強をきちんとしないと無用な時間をかけてパフォーマンスの悪いプログラムを作り続けることになります。
guest

0

約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.

総合スコア7914

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2020/03/26 09:01

insertでselectを使用する場合はvaluesは使用しません(というか文法エラーです)
Y.H.

2020/03/26 09:05

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

2020/03/26 10:18

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

0

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

SqlBulkCopy.WriteToServer メソッド

投稿2020/03/26 09:33

編集2020/03/26 09:38
polupolu_0218

総合スコア65

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

matsutake

2020/03/26 10:17

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

0

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 編集

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問