トップ回答者
Linq to SQL + SqlServerCe 3.5 で SubmitChangesを繰り返し行うことでクエリ速度が遅くなる

質問
-
お世話になります。
Linq to SQL と SqlServerCe 3.5 を組み合わせたWindowsフォームアプリケーションをVisual Basic 2010で開発しております。
(開発・テスト環境)================================
OS:Windows 7 Professional
言語:Visual Basic 2010
ターゲットFw:.NET Framework 3.5
SqlServerCe(System.Data.SqlserverCe.dll)バージョン:3.5.1
================================================
DataContextのSubmitChangesメソッドを呼び出した際に、異常に時間が掛かることがあり、以下のようなサンプルコードを作成してみました。
Imports System.Data.Linq Imports System.Data.Linq.Mapping Imports System.Data.SqlServerCe Public Class Form1 <Table(Name:="Sample")> _ Public Class Sample <Column(Name:="Id", DbType:="INT IDENTITY(1,1)", IsPrimaryKey:=True, AutoSync:=AutoSync.OnInsert, IsDbGenerated:=True)> _ Public Property Id As Integer <Column(Name:="Name", DbType:="NVARCHAR(100)", UpdateCheck:=UpdateCheck.Always)> _ Public Property Name As String <Column(Name:="Data", DbType:="NUMERIC(38,15)", UpdateCheck:=UpdateCheck.Always, CanBeNull:=False)> _ Public Property Data As Decimal Public Sub New() Me.Id = -1 End Sub Public Sub New(ByVal name As String, ByVal data As Decimal) Me.New() Me.Name = name Me.Data = data End Sub End Class Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click Using con As New SqlCeConnection("Data Source='" & Application.StartupPath & "\sample_db.sdf';Password='********';") con.Open() If con.State = ConnectionState.Open Then Dim dctx As New DataContext(con) Dim q_Sample As Table(Of Sample) = dctx.GetTable(Of Sample)() Dim sw As New Stopwatch For i As Integer = 0 To 1000000 q_Sample.InsertOnSubmit(New Sample("item" & i, i * 10)) sw.Reset() sw.Start() dctx.SubmitChanges() '(※1) 'dctx.Dispose() 'dctx = New DataContext(con) 'q_Sample = dctx.GetTable(Of Sample)() sw.Stop() Debug.Print(i & "件目:" & sw.ElapsedMilliseconds & "ミリ秒") Next con.Close() End If End Using End Sub End Class
上記のコードをそのまま実行すると、6万件のデータをデータベースに追加するのに約7時間も掛かってしまうのですが、1件目のSubmitChangesが1~2msで終了するのに対し、6万件目のSubmitChangesは、800~1100msも掛かってしまいます。そこで、※1のコメントアウトを解除し、SubmitChangesを行う毎にDataContextを再インスタンス化すると、1~3msで安定します。
上記のことから、InsertOnSubmitまたはSubmitChangesを行うことで、DataContextが内部に何らかのデータを蓄積し、それがパフォーマンスの劣化に繋がっているのではないかと予測しているのですが、蓄積データを開放する方法が分からず、そもそも本当に予測が正しいのか判断がつきません。(DataContextのGetChangeSetで得られるデータは、SubmitChanges実行後に全てCount=0になっていることは確認しました)
SubmitChanges実行後に、都度DataContextの再インスタンス化を行わなければいけない仕様なのであれば諦めますが、そういうものなのでしょうか?
お手数をお掛けしますが、お分かりになる方がいらっしゃいましたらご教授くだいますようお願い致します。m(_ _)m
回答
-
SubmitChangesの所要時間増加の原因がレコード数なのであれば、上記の結果は前のサンプルの実行結果に近くなるはずですが、1件目と100件目の所要時間の差異はあまり見られません。
totojo様が仰られたように、前のサンプルがSubmitChanges時に全レコードをループしているのに対し、上記のコードは新しく追加された2000レコードのみをループしているということなのでしょうか?もし、ブラックボックスにまで踏み込んだご質問でしたら申し訳ないのですが、何分無知なもので、もしご存知であればご教授願います。
データベース上のレコード数ではなく、DataContext が保持するデータ数が関係していると思います。(trapemiya さんの、内部キャッシュとの指摘に同意です。)
DataContext の内部で何をやっているかが気になるのであれば、さすがにソース コードを読むしかないのでは。
.NET Framework Libraries
http://referencesource.microsoft.com/netframework.aspx
ちらっと見た雰囲気だと、InsertOnSubmit ではトラッキング データを追加していて、SubmitChanges ではデータ自体は消さずにステータスだけ変更しているように見えます。
ですので、DataContext のインスタンスを使い続けると、SubmitChanges で処理する内部レコード数が増え続けてしまうのではないでしょうか。
# ClearCache というメソッドがあるけど、internal だなぁ...。- 回答としてマーク tssys 2013年3月11日 10:56
すべての返信
-
そもそも SubmitChanges は大量の INSERT には向いていないと思います。
Why massive inserts using SubmitChanges lack in performance. - Tips and tricks from a Developer Support perspective. - Site Home - MSDN Blogs
http://blogs.msdn.com/b/spike/archive/2009/09/01/why-massive-inserts-using-submitchanges-lack-in-performance.aspx
残念ながら SqlServerCe には SqlBulkCopy がありませんが、それを回避するようなライブラリも公開されています。
SQL Compact Bulk Insert Library - Home
http://sqlcebulkcopy.codeplex.com/
ところで、元々の問題である「SubmitChanges で異常に時間がかかることがある」現象は、大量データの INSERT に起因するものですか? -
totojo様
やはり今回の現象は仕様によるもので「Linq To SQLは更新系のアプリケーションには向かない」といわれる所以なのでしょうか・・・
早々のご回答ありがとうございます。
SQL Compact Bul Insert Library の存在は知りませんでした!調査不足ですね・・・(^^;)
早速評価してみたいと思います。ありがとうございます。
ちなみに、「SubmitChanges で異常に時間がかかることがある」現象の起因はご指摘のとおり大量データ(200000件程度)のINSERTに起因するものですが、疑問としている部分は大量データのINSERTに時間がかかること自体ではなく、同じデータ量を繰り返しINSERTする場合に、同じデータ量にも係わらずSubmitChangesに時間が掛かるのはなぜなのかということです。
例えば、200000件のデータを追加する場合に、サンプルのFor文の中身を以下のように置換えますと6分程度で処理は完了するのですが、問題は2000件のInsertに対するSubmitChanges に要する時間の差異です。
For i As Integer = 0 To 99 For j As Integer = 0 To 1999 q_Sample.InsertOnSubmit(New Sample("item" & (i * 2000 + j).ToString, i * 2000 + j)) Next sw.Reset() sw.Start() dctx.SubmitChanges() sw.Stop() Debug.Print((i + 1) & "件目:" & sw.ElapsedMilliseconds & "ミリ秒") Next
■結果
1件目:2526ミリ秒
2件目:2513ミリ秒
3件目:2437ミリ秒
~省略~
98件目:5206ミリ秒
99件目:5473ミリ秒
100件目:4334ミリ秒
何故SubmitChanges を繰り返し実行することで、同一データ量にも係わらずこれほどの差異が発生するのでしょうか・・・?
-
SubmitChanges が呼ばれたタイミングでレコードを走査して、変更トラッキングした内容をチェックして、適切な SQL を組み立てて発行していると思いますので、レコード数が増えるにつれて SubmitChanges の所要時間が伸びるのは仕方がない気がします。(SubmitChanges の中で全レコードをループしているはずですので。)
linq to sql - Several SubmitChanges in succession very slow - Stack Overflow
http://stackoverflow.com/questions/2973159/several-submitchanges-in-succession-very-slow
LINQ to SQL (パート 4 - データベースの更新) - ScottGuさんのブログ翻訳
http://chicasharp.net/scottgu/result2.aspx?target=LINQ+to+SQL+(%E3%83%91%E3%83%BC%E3%83%88+4+-+%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%9B%B4%E6%96%B0) -
totojo様
そうしますと、SubmitChangesの所要時間増加の原因は、レコード数の増加によるものということなのでしょうか?
例えば、先ほど投稿させていただいたコードを以下のように修正して実行してみました。
For i As Integer = 0 To 99 For j As Integer = 0 To 2000 q_Sample.InsertOnSubmit(New Sample("item" & (i * 2000 + j).ToString, i * 2000 + j)) Next sw.Reset() sw.Start() dctx.SubmitChanges() '以下3行追加 dctx.Dispose() dctx = New DataContext(con) q_Sample = dctx.GetTable(Of Sample)() sw.Stop() Debug.Print((i + 1) & "件目:" & sw.ElapsedMilliseconds & "ミリ秒") Next
■結果
1件目:2394ミリ秒
2件目:2227ミリ秒
3件目:2433ミリ秒
~省略~
98件目:1867ミリ秒
99件目:1933ミリ秒
100件目:2054ミリ秒SubmitChangesの所要時間増加の原因がレコード数なのであれば、上記の結果は前のサンプルの実行結果に近くなるはずですが、1件目と100件目の所要時間の差異はあまり見られません。
totojo様が仰られたように、前のサンプルがSubmitChanges時に全レコードをループしているのに対し、上記のコードは新しく追加された2000レコードのみをループしているということなのでしょうか?もし、ブラックボックスにまで踏み込んだご質問でしたら申し訳ないのですが、何分無知なもので、もしご存知であればご教授願います。
-
-
trapemiya様
ご回答ありがとうございます。
以下、6通り試してみましたが、効果はありませんでした。
dctx.Refresh(RefreshMode.KeepChanges) dctx.Refresh(RefreshMode.KeepCurrentValues) dctx.Refresh(RefreshMode.OverwriteCurrentValues) dctx.Refresh(RefreshMode.KeepChanges, q_Sample) dctx.Refresh(RefreshMode.KeepCurrentValues, q_Sample) dctx.Refresh(RefreshMode.OverwriteCurrentValues, q_Sample)
私も状況から推測すると、内部キャッシュの増大による影響なのではないかと考えておりますが、確証がありません。
MSDNの記述を見ますと、DataContextのRefreshメソッドについては、キャッシュをクリアするような表記は見られませんが、そのような動作をするメソッドなのでしょうか?http://msdn.microsoft.com/ja-jp/library/bb358706(v=vs.90).aspx
-
SubmitChangesの所要時間増加の原因がレコード数なのであれば、上記の結果は前のサンプルの実行結果に近くなるはずですが、1件目と100件目の所要時間の差異はあまり見られません。
totojo様が仰られたように、前のサンプルがSubmitChanges時に全レコードをループしているのに対し、上記のコードは新しく追加された2000レコードのみをループしているということなのでしょうか?もし、ブラックボックスにまで踏み込んだご質問でしたら申し訳ないのですが、何分無知なもので、もしご存知であればご教授願います。
データベース上のレコード数ではなく、DataContext が保持するデータ数が関係していると思います。(trapemiya さんの、内部キャッシュとの指摘に同意です。)
DataContext の内部で何をやっているかが気になるのであれば、さすがにソース コードを読むしかないのでは。
.NET Framework Libraries
http://referencesource.microsoft.com/netframework.aspx
ちらっと見た雰囲気だと、InsertOnSubmit ではトラッキング データを追加していて、SubmitChanges ではデータ自体は消さずにステータスだけ変更しているように見えます。
ですので、DataContext のインスタンスを使い続けると、SubmitChanges で処理する内部レコード数が増え続けてしまうのではないでしょうか。
# ClearCache というメソッドがあるけど、internal だなぁ...。- 回答としてマーク tssys 2013年3月11日 10:56
-
totojo様
ありがとうございます。
やはり、これ以上はソースコードを読むしかないのでしょうか・・・(^^;)
ところで教えていただいたInrernalなClearCacheメソッドですが、完全に興味本位でReflection呼び出ししてみました(笑)'*追加(1) Dim mi As Reflection.MethodInfo = GetType(DataContext).GetMethod("ClearCache", Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic) For i As Integer = 0 To 99 For j As Integer = 0 To 1999 q_Sample.InsertOnSubmit(New Sample("item" & (i * 2000 + j).ToString, i * 2000 + j)) Next sw.Reset() sw.Start() dctx.SubmitChanges() '*追加(2) mi.Invoke(dctx, Nothing) sw.Stop() Debug.Print((i + 1) & "件目:" & sw.ElapsedMilliseconds & "ミリ秒") Next
■結果
1件目:2068ミリ秒
2件目:2037ミリ秒
3件目:2281ミリ秒~省略~
98件目:2252ミリ秒
99件目:2074ミリ秒
100件目:2131ミリ秒ということで、御二方よりご指摘いただいたとおり、内部キャッシュがパフォーマンスに影響しているということで間違いないようです。
ただ、上記の方法でClearCacheメソッドを使用してもよいかというと、やはり駄目なんでしょうね・・・
Inrernalな時点で、開発者からすれば外部から呼び出されたくないということですし、呼び出した場合他にどういった影響が出るのかわかりませんので。
内部キャッシュが自由にクリアできないということは、SubmitChangesで内部キャッシュがクリアされないのも仕様のようですね。
Databaseに連続でクエリを投げる場合にはSqlCeCommandやExecuteCommandを使用するか、DataContextを再インスタンス化することで対応したいと思います。
totojo様、trapemiya様、ここまでお付き合いいただきまして本当にありがとうございました。m(_ _)m