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

Question

EDIT: the issue below has been fixed in the Entity Framework 6.

Running the code below takes a disappointing 2 minutes and 10 seconds. Running it a second time takes 6.5 minutes. This question is related to this one

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()
        MyBase.New()
        One = "arerjlwkerjglwejrglwergoiwerhgiowehrowerlwelfvbwlervbowerghpiweurhgpiwuerviiervljwebbrlvjnepvjnweprvupiweurv"
        Two = "w;lrjgwwergkjwervgjwelrgjhwelghlwekglwergiuwehrgwjergjwervgjwerjgnwekrngpwergjpowergllwejrnglkwerngpoierhpiiuewrpjwenrwenrv;lwenrvkjernpgpsrvpi"

    End Sub

    'ID'
    Public Property ID As Integer

    'NATIVE PROPERTIES'
    Public Overridable Property One As String
    Public Overridable Property Two As String

    'NAVIGATION PROPERTIES'

End Class 

Moving the Using block inside the second level interation and calling SaveChanges there makes it only worse.

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?

Cabrera

Is it possible for you to test whether this also happens in a server version of sql server (express)? – Laity

@GertArnold it is. I will let you know later this day. I read that this problem seems to be specific to the Compact version though. – Cabrera

Could you share a CREATE TABLE script? (Created by the Sql Compact Toolbox) – Modena

Connect the toolbox to thev sdf file in your bin/bebug folder, and create the script – Modena

Interestingly, the same code runs in milliseconds with ObjectContext - I will try to dig further – Modena

@Modena Thanks! We are on the verge of implementing this database and the decision how to generate the primary keys is of course pivotal. – Cabrera

I have just done some testing - use Guid.NewGuid() is my suggestion – Modena

@Modena That amounts to using a client generated ID. I know that that is a lot faster. It seems that this problem must some kind of bug. Why else do SQL Express and using ObjectContext not suffer from it? – Cabrera

Yes, it is most likely a bug, but you do not have time to wait for a fix. Log an issue on the Codeplex site or connect – Modena

@Modena I logged the issue on CodePlex. Will you upvote it? – Cabrera

Answer 1

I found the issue, it is caused by the statement to get the generated id causing table scans, I propose a fix here, tested with 4000 entities, down from 17 secs to 2 secs. http://entityframework.codeplex.com/workitem/857 - it will be include in the EF6 build after Alpha 3

Modena

Or other approaches were suggested, expecting it to be by design - turned out to be by poor design – Modena

The issue was fixed for EF 6. – Cabrera