Exponentially deteriorating performance on inserts in SQL Server Compact 4.0 tables with an Identity column RRS feed

  • Question

  • Running the code below takes a staggering 2 minutes and 10 seconds. Running it a second time takes 6.5 minutes,

    Private Sub RunTest() Dim sw As New Stopwatch sw.Restart() Using db As New TestDB db.Configuration.AutoDetectChangesEnabled = False For n = 1 To 100 For m = 1 To 100 db.Tops.Add(New Top) Next Next db.SaveChanges() End Using MsgBox(sw.Elapsed.ToString) End Sub

    The Entity:

    Public Class Top
        Public Sub New()
            One = "arerjlwkerjglwejrglwergoiwerhgiowehrowerlwelfvbwlervbowerghpiweurhgpiwuerviiervljwebbrlvjnepvjnweprvupiweurv"
            Two = "w;lrjgwwergkjwervgjwelrgjhwelghlwekglwergiuwehrgwjergjwervgjwerjgnwekrngpwergjpowergllwejrnglkwerngpoierhpiiuewrpjwenrwenrv;lwenrvkjernpgpsrvpi"
        End Sub
        Public Property ID As Integer
        Public Overridable Property One As String
        Public Overridable Property Two As String
    End Class

    TestDB just inherits DBContext without any further configuration.

    When I disable database generation of the Top.ID property and supply the ID myself the performance improves a thirty fold.

    This problem makes using Database generated ID's in SQL Server Compact impossible. Is there a solution other than using client side generated IDs?


    Thursday, February 7, 2013 10:18 PM


All replies

  • Hi,

    Please verify which statement is slow.

    Thank you.

    Call me ghost for short, Thanks
    To get the better answer, it should be a better question.

    Monday, February 11, 2013 6:03 AM
  • SaveChanges
    Monday, February 11, 2013 6:56 AM
  • Hi Aner,

    I tested your code on my side. It also takes a long time maybe 4 or 5 minutes. I think this problem is related to large data and SQL CE. Please check if it also takes so long time for SQL Server.

    Best Regards,

    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 14, 2013 9:12 AM
  • Hello Alexander,

    What do you mean by "Large Data" I created a test enviroment with one table, two string properties. It has already been established that the problem is restricted to SQL Server Compact 4.0 (see the other post in this forum about this very problem, which seems to have been ignored for more than year). The problem is related to the Entity Framework + SQL Server Compact because when using ADO.Net/BulkCopy the problem does not materialize.

    As this seemingly is a bug somewhere, why hasn't it been addressed since 2011?



    Thursday, February 14, 2013 3:16 PM
  • ErikEJ has established that this is indeed a bug in the interaction between EF+SQL Server Compact. He proposed a solution at http://entityframework.codeplex.com/workitem/857
    The bug should have been fixed for EF 6.0
    • Marked as answer by Aner Friday, February 15, 2013 2:31 PM
    • Edited by Aner Wednesday, July 31, 2013 10:57 AM
    Friday, February 15, 2013 2:30 PM