带有Identity列的SQL Server Compact 4.0表中插入的指数性能下降 [英] Exponentially deteriorating performance on inserts in SQL Server Compact 4.0 tables with an Identity column

问题描述

编辑：下面的问题已在实体框架6中修复。

运行下面的代码将失望2分10秒。第二次运行需要6.5分钟。这个问题与这一个

  Private Sub RunTest（）
 Dim sw As New Stopwatch 
 sw.Restart（）
使用db作为新的TestDB 
 db.Configuration.AutoDetectChangesEnabled = False 
对于n = 1至100 
对于m = 1至100 
 db.Tops.Add（新上）
下一个
下一个
 db.SaveChanges（）
结束使用
 MsgBox（sw.Elapsed.ToString）
 End Sub 
  

实体：

 公共类顶级
公共子新（）
 MyBase.New（）
一= = wergkjwervgjwelrgjhwelghlwekglwergjwewgwjergjwervgjwerjgnwekrngpwergjpowergllwejrnglkwerngpoierhpiiuewrpjwenrwenrv; lwenrvkjernpgpsrvpi
 
 End Sub 
 
'ID'
公共属性ID为整数
 
'内部属性'
公共覆盖属性One As String 
公共可覆盖属性Two As String 
 
'导航属性'
 
结束类
  / pre> 
 
 
在第二级交互中移动使用块，并调用SaveChanges，只会更糟。
 
 
 
 TestDB只是继承DBContext而没有任何进一步的配置。当我禁用Top.ID属性的数据库生成并自己提供ID时，性能提高了三十倍。这个问题使得在SQL Server Compact中使用数据库生成的ID是不可能的。除了使用客户端生成的ID以外还有其他解决方案吗？ 
解决方案
我发现这个问题，是由语句导致生成的id导致表扫描，我建议在这里修复，用4000个实体测试，从17秒到2秒。  http://entityframework.codeplex.com/workitem/857   - 将包含在EF6中在Alpha 3之后构建
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?  
解决方案
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

                        
这篇关于带有Identity列的SQL Server Compact 4.0表中插入的指数性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！