none
Code First & SQL Compact 4 performance issue

    Question

  • Hi all,
    I'm encountering a performance issue using Code First & SQL Compact 4.
    I'm creating 10.000 instances of an entity class, adding them to the DbContext, then call SaveChanges to save them to the database.

    Using SQL Compact as Database, the Savechanges() method takes about 124 seconds to execute, while using SQL Express 2008 it takes about 3 seconds!!!!

    I just created a very simple sample to see this behaviour, follows the Entity and the DbContext classes

     

    The Entity class

    //   The Entity
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Windows.Media.Media3D;
    
    namespace TestCodeFirst
    {
        [Table("ClassiTest")]
        public class TestClass
        {
            [Key]
            [DatabaseGenerated(System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.Identity)]
            public long rid { set; get; }
    
            public int IDElemento { set; get; }
            public double X { set; get; }
            public double Y { set; get; }
            public double Z { set; get; }
            public double Massa { set; get; }
    
            public TestClass()
                : base()
            { }
        }
    }
    
    

    The DbContext

    //    The DbContext
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    namespace TestCodeFirst
    {
        public class ContestoTest : DbContext
        {
            public DbSet<TestClass> ClassiTest { get; set; }
    
            public ContestoTest()
                : base()
            {
                costruttorecomune();
    
            }
    
            public ContestoTest(string _connstring)
                : base(_connstring)
            {
                costruttorecomune();
            }
    
            private void costruttorecomune()
            {
                this.Configuration.ProxyCreationEnabled = true;
                this.Configuration.AutoDetectChangesEnabled = false;
                this.Configuration.LazyLoadingEnabled = true;
                this.Configuration.ValidateOnSaveEnabled = false;
    
                DateTime iniz = DateTime.Now;
                this.ClassiTest.Load();
                Console.WriteLine(string.Format(" *****  Load ClassiTest {0} sec", (DateTime.Now - iniz).TotalSeconds));
            }
        }
    }
    
    


    I have done some test to check the Compact version performance and created a for loop that perform 10.000 SQL insert into statements against the same SQL Compact sdf file (and table) and the loop take about 1.5 seconds to perform 10.000 times 2 SQL statements: the insert one and a Select @@identity to retrieve the indentity key just created.

    So where's the problem? is Code First or SQL Compact?
    I don't understand why Code First is so slow to insert a lot of entities using SQL Compact.

    Below you find a Window code to reproduce it (change the connection string for SQL Compact file)

    The Window to test

    <Window x:Class="TestCodeFirst.Window1"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            Title="Window1" Height="300" Width="286">
        <Grid>
            <Button Content="Code First Insert" Height="23" HorizontalAlignment="Left" Margin="57,99,0,0" Name="cmdinsert" VerticalAlignment="Top" Width="178" Click="cmdinsert_Click" />
            <Label Content="N° Items to insert" Height="28" HorizontalAlignment="Left" Name="label1" VerticalAlignment="Top" />
            <TextBox Height="23" HorizontalAlignment="Left" Margin="109,0,0,0" Name="txtnitems" VerticalAlignment="Top" Width="88" Text="10000" />
            <RadioButton Content="SQL Compact" Height="16" HorizontalAlignment="Left" Margin="12,34,0,0" Name="optcompact" VerticalAlignment="Top" GroupName="g1" IsChecked="True" />
            <RadioButton Content="SQL Express" Height="16" HorizontalAlignment="Left" Margin="12,56,0,0" Name="optexpress" VerticalAlignment="Top" GroupName="g1" />
            <Button Content="Insert Loop" Height="23" HorizontalAlignment="Left" Margin="57,221,0,0" Name="cmdinsertloop" VerticalAlignment="Top" Width="178" Click="cmdinsertloop_Click" />
        </Grid>
    </Window>
    
    

    using System;
    using System.Data.Entity.Infrastructure;
    using System.Windows;
    using System.Data.SqlServerCe;
    
    namespace TestCodeFirst
    {
        public partial class Window1 : Window
        {
            ContestoTest ctx { set; get; }
            string connCE = "Data Source=E:\\svn\\Rep_Prove\\Visual studio\\Wpf\\App2\\App2\\App2\\bin\\Debug\\pippo.sdf";
    
            public Window1()
            {
                InitializeComponent();
                System.Data.Entity.Database.SetInitializer<ContestoTest>(new System.Data.Entity.DropCreateDatabaseAlways<ContestoTest>());
            }
    
            private void cmdinsert_Click(object sender, RoutedEventArgs e)
            {
                bool SQLCompact = this.optcompact.IsChecked.Value;
    
                if (SQLCompact)
                {
                    System.Data.Entity.Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
                    this.ctx = new ContestoTest(connCE);
                }
                else
                {
                    System.Data.Entity.Database.DefaultConnectionFactory = new SqlConnectionFactory();
                    this.ctx = new ContestoTest();
                }
    
                int n = int.Parse(this.txtnitems.Text);
                DateTime iniz = DateTime.Now;
                for (int i = 0; i < n; i++)
                {
                    TestClass nn = new TestClass() { IDElemento = i, X = i * 2, Y = i * 3, Z = i * 4 };
                    nn.Massa = 100;
                    ctx.ClassiTest.Add(nn);
                }
    
                this.ctx.SaveChanges();
    
                Console.WriteLine(string.Format("Creazione e aggiunta di {0} nodi: {1} ", n, (DateTime.Now - iniz).TotalSeconds));
            }
    
            private void cmdinsertloop_Click(object sender, RoutedEventArgs e)
            {
                SqlCeConnection oconn = new SqlCeConnection(connCE);
                SqlCeCommand ocmd = new SqlCeCommand("insert into ClassiTest (IDElemento, X , Y , Z, massa ) values (@id, @x, @y, @z, @massa) ", oconn);
    
                ocmd.Parameters.Add(new SqlCeParameter("@id", 0));
                ocmd.Parameters.Add(new SqlCeParameter("@x", 0));
                ocmd.Parameters.Add(new SqlCeParameter("@y", 0));
                ocmd.Parameters.Add(new SqlCeParameter("@z", 0));
                ocmd.Parameters.Add(new SqlCeParameter("@massa", 0));
    
                SqlCeCommand ocmdS = new SqlCeCommand("Select @@identity ", oconn);
    
                oconn.Open();
    
                int n = int.Parse(this.txtnitems.Text);
    
                DateTime iniz = DateTime.Now;
    
                for (int i = 0; i < n; i++)
                {
                    ocmd.Parameters["@id"].Value = i;
                    ocmd.Parameters["@x"].Value = i * 2;
                    ocmd.Parameters["@y"].Value = i * 3;
                    ocmd.Parameters["@z"].Value = i * 4;
                    ocmd.Parameters["@massa"].Value = i * 5;
    
                    ocmd.ExecuteNonQuery();
                    object ret = ocmdS.ExecuteScalar();
                }
                oconn.Close();
                Console.WriteLine(string.Format(" TOTAL time SQLCOMPACT {0} ", (DateTime.Now - iniz).TotalSeconds));
            }
        }
    }
    
    

     

    Any help is appreciated

    Wednesday, October 26, 2011 3:18 PM

Answers

All replies

  • Hi,

    I think you can try to use "EF Power tools " to get the pre-compiled view to import the performance:

    http://blogs.msdn.com/b/adonet/archive/2011/05/18/ef-power-tools-ctp1-released.aspx

    ------------------------

    Optimize Entity Data Model
    Generates pre-compiled views used by the EF runtime to improve start-up performance. Adds the generated views file to the containing project.

    • View compilation is discussed in the Performance Considerations article on MSDN.
    • If you change your Code First model then you will need to re-generate the pre-compiled views by running this command again.

    ------------------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 27, 2011 5:41 AM
    Moderator
  • Hi,

    thanks for your answer. The tool crash VS (I run it against the simple DbContext of my previous post but it crash on a bigger one too)....

    I got the message "An error occurred while trying to generate views for the DbContext type ContestoTest. See the Output window for details"

    In the Output Window  I got:

     

    ------ Inizio compilazione: Progetto: TestCodeFirst, Configurazione: Debug x86 ------
      TestCodeFirst -> D:\franco\TestCodeFirst\TestCodeFirst\TestCodeFirst\bin\Debug\TestCodeFirst.exe
    ========== Compilazione: 1 completate o aggiornate, 0 non riuscite, 0 ignorate ==========

     

    and the information about the crash are:

     

    Firma problema:
      Nome evento problema: CLR20r3
      Firma problema 01: devenv.exe
      Firma problema 02: 10.0.40219.1
      Firma problema 03: 4d5f2a73
      Firma problema 04: EF4PowerTools
      Firma problema 05: 1.0.0.0
      Firma problema 06: 4dcdbea3
      Firma problema 07: cf
      Firma problema 08: 11
      Firma problema 09: System.AggregateException
      Versione SO: 6.1.7601.2.1.0.256.1
      ID impostazioni locali: 1040

    Ulteriori informazioni sul problema:
      LCID: 1040

     

    The project is targeted to "Microsot Entity Framework June 2011 CTP", is the right EF version?

    Thanks

    Edit: in any case, why there are so differences between the Compact and the Express using Code First? the Compact version is even fast for thousand of Insert Into statements, so the problem must be in Code First


    • Edited by FraCal Thursday, October 27, 2011 10:20 AM
    Thursday, October 27, 2011 8:33 AM
  • FraCal,

    I got the same scenario when I install June 2011 CTP, after uninstalling the CTP, I find my Ado.net entity framework Item template missed from my VS, you can refer the "Readme" here: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26660

    ---------------

    Known issues and limitations
    We recommend installing the Microsoft Entity Framework and SQL Server Tools for Data Framework June 2011 CTP in a non-production environment to avoid any risk associated with installing and uninstalling pre-release software.
    If the SQL Server Tools for Data Framework CTP is uninstalled, existing functionality installed with Visual Studio 2010 (such as the Entity Designer) will no longer be available.

    --------------

    You can use SQL Profiler to watch where code the performance hit.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 01, 2011 6:47 AM
    Moderator
  • Hi Alan, thanks for the respond.

    I removed all EF installations, reapplied VS SP1, then installed EF 4.1 Update 1 and the tool Optimize Entity Data Model now works.

    But now I have problems at runtime.

    Using SQL Express everything works, while using SQL Compact 4.0 I have the runtime error:

    "An exception occurred while initializing the database. See the InnerException for details."

    And the inner exceptions (translated):
    "The information on mapping and metadata for EntityContainer 'ContestoCodeFirst' no longer correspond to those used to create the pre-generated views."

    If I remove the pregenerated views from the project everything is fine (except for the performace issue as for my first post).

    thanks

    Franco

    Thursday, November 03, 2011 8:34 AM
  • Hi Franco,

    Thanks for your feedback.

    It seems the tool just works for SQL Server Database. From the Performance considerations, we can refer this link to generate views: http://msdn.microsoft.com/en-us/library/bb896240.aspx, you should work it based on EDMX file, you can refer this link: http://blogs.msdn.com/b/adonet/archive/2011/03/15/ef-4-1-model-amp-database-first-walkthrough.aspx

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, November 04, 2011 7:19 AM
    Moderator
  • This is a late answer but it may help others having the same sort of issue. We also noticed a similar problem, if the Identity column is replaced and the PK is maintained by the application the performance is 40x better !!!. We managed to insert 8k instances in less that one second when the mentioned approach is taken. It was taken 40 secs with the Identity column.

    Wednesday, July 18, 2012 8:48 AM
  • I ran into the same issue using a SQL Compact 4.0 database.  I'm trying to insert 75,000 records into a database and it takes forever using the code first Entity.DbContext.  I tried making the exact same inserts with Linq to SQL and it was about 20 times faster.  In order to try and get to the bottom of the issue I used code from http://jkowalski.com/2010/04/23/logging-sql-statements-in-entity-frameworkcode-first/ (since you can't see a trace of the generated SQL using DbContext the way that you can with the Linq-to-SQL DataContext.Log).  Here's a comparison of the SQL:

    Code First SQL:

    insert [UnitInfos]([JobName], [SalesOrderLine], [PlantNumber], [UnitTag], [ShipDate])
    values (@0, @1, @2, @3, @4); 
    select [ID]
    from [UnitInfos]
    where [ID] = @@IDENTITY

    Linq to SQL:

    INSERT INTO [UnitInfos]([JobName], [SalesOrderLine], [PlantNumber], [UnitTag], [ShipDate]) VALUES (@p0, @p1, @p2, @p3, @p4)

    SELECT CONVERT(Int,@@IDENTITY) AS [value]

    As you can see, the code first SQL method has to actually find the row in the database before it can return the new identity value.  This means that when your database is initially empty both queries perform about the same, but as you add more records the code first SQL performs worse and worse.  If you change all of your ID columns to GUIDs instead of integers so that no database generated values need to be retrieved after every insert, everything runs much much faster.


    • Edited by CoderNate Monday, October 08, 2012 9:53 PM
    Monday, October 08, 2012 9:52 PM