none
Linq to SQL + SqlServerCe 3.5 で SubmitChangesを繰り返し行うことでクエリ速度が遅くなる RRS feed

  • 質問

  • お世話になります。

    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

    2013年3月8日 2:06

回答

  • 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
    2013年3月11日 7:23

すべての返信

  • そもそも 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 に起因するものですか?
    2013年3月8日 6:46
  • totojo様

    早々のご回答ありがとうございます。

    SQL Compact Bul Insert Library の存在は知りませんでした!調査不足ですね・・・(^^;)
    早速評価してみたいと思います。ありがとうございます。

    やはり今回の現象は仕様によるもので「Linq To SQLは更新系のアプリケーションには向かない」といわれる所以なのでしょうか・・・
    ちなみに、「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 を繰り返し実行することで、同一データ量にも係わらずこれほどの差異が発生するのでしょうか・・・?

    2013年3月8日 9:21
  • 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)
    2013年3月8日 10:33
  • 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レコードのみをループしているということなのでしょうか?

    もし、ブラックボックスにまで踏み込んだご質問でしたら申し訳ないのですが、何分無知なもので、もしご存知であればご教授願います。

    2013年3月8日 11:37
  • おそらく、内部キャッシュの増大による遅延でないでしょうか? 新しくDataContextを作り直した場合はキャッシュがクリアされますから、遅延が発生しないのではないかと思います。内部キャッシュの影響かどうかを見るために、DataContextのRefreshメソッドを呼ばれてみてはいかがでしょうか? Refreshメソッドで内部キャッシュが新しく作り直されるはずです。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2013年3月11日 1:17
    モデレータ
  • 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

    2013年3月11日 5:50
  • 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
    2013年3月11日 7:23
  • 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


    2013年3月11日 10:56