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?

Is it possible for you to test whether this also happens in a server version of sql server (express)? — Gert Arnold, Feb 08 '13 at 08:50
@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. — Dabblernl, Feb 08 '13 at 08:59
Could you share a CREATE TABLE script? (Created by the Sql Compact Toolbox) — ErikEJ, Feb 08 '13 at 09:14
Connect the toolbox to thev sdf file in your bin/bebug folder, and create the script — ErikEJ, Feb 08 '13 at 09:20
Interestingly, the same code runs in milliseconds with ObjectContext - I will try to dig further — ErikEJ, Feb 08 '13 at 12:01
@ErikEJ Thanks! We are on the verge of implementing this database and the decision how to generate the primary keys is of course pivotal. — Dabblernl, Feb 08 '13 at 12:40
I have just done some testing - use Guid.NewGuid() is my suggestion — ErikEJ, Feb 08 '13 at 12:53
@ErikEJ 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? — Dabblernl, Feb 08 '13 at 13:22
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 — ErikEJ, Feb 08 '13 at 13:29

ErikEJ · Accepted Answer · 2013-02-28T07:41:23.100

3

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

edited Feb 28 '13 at 07:41

answered Feb 15 '13 at 13:26

ErikEJ

40,951
5
75
115

Or other approaches were suggested, expecting it to be by design - turned out to be by poor design – ErikEJ Feb 17 '13 at 02:25
The issue was fixed for EF 6. – Dabblernl Mar 02 '13 at 07:51

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

1 Answers1

Linked