Performance drops on Visual Studio 2017

[Gabbbou]

I integrated Effort.EF6 to our solution. Using Resharper to run my tests, all 700 tests ran in 5 minutes. Which is good. Then, I ran the tests using the test runner from Visual Studio 2017 and it took 45 minutes to run all the tests.

After some profiling I discovered that the lines being slow are the ones where we save changes on context.
Adding and saving a small collection of objects with Resharper takes around 10 ms, when on VS test runner is takes 60 ms.

I use EF6 model first. I create a transient connection before each tests:
_dbConnection = Effort.EntityConnectionFactory.CreateTransient("name=MyEntities");
DataContext = new MyEntities(_dbConnection);

I use Visual Studio Enterprise 2017 Version 15.5.2 .NEt Framework Version 4.6.01590

[Gabbbou]

Just tested with VS2015 and it takes 25mins... I'm so confuse.,,

[JonathanMagnan]

Hello @Gabbbou ,

Honestly, I'm not sure what we can do here.

It seems more an issue about how Unit Test are handled in VS2015, VS2017, and Resharper than an issue with the library.

Best Regards,

Jonathan

---

Help us to support this library: Donate

[Gabbbou]

Hi!

I understand how it may appear as a problem from the Visual Studio runner.
That being said, we also have a big test suite not touching the database that is as fast on VS as on Resharper.
No unit test, not using an external dependancy, takes a long time using VS test runner. No one could use the tool if it was the case.

Also, Effort is built for EF whih is for C# and I guess most poeple use VS for C#. The lib being that slow makes it unusable. Will it be like that for every futur version of VS? I don't know, but if it's the case...

We also have this problem on our build machine and other dev setup.

Thanks !

[JonathanMagnan]

Hello @Gabbbou ,

We will look at it to check if we can find something. Giving a try worth it ;)

Best Regards,

Jonathan

---

Help us to support this library: Donate

[Gabbbou]

I will give you news of my investigations. For now, it's really when we do the context.SaveChanges() that takes time.

Small question: When you add to a collection and SaveChanges, is it only the InsertCommandAction.ExecuteDataReader that is called?

[JoeShmoel]

Gabbbou, did you find a solution to Efforts poor performance?

[JonathanMagnan]

Hello @Gabbbou , @JoeShmoel ,

We recently released the v1.3.8'

In this version, we added a new method ClearTables that clear all data and reset the identity. Only the __migrationHistory tables keep his data. So it allows to speed up test by A LOT when you re-use the same connection.

var connection = (EffortConnection)DbConnectionFactory.CreateTransient();

// ...code...

connection.ClearTables();

If you have the chance to try it, could you let me know if you still have the issue?

Best Regards,

Jonathan

[fsdschmidt]

We have also big performance issues since vs 2017. We use the EntityConnectionFactory where the EffortConnection is encapsulated. And we dispose our EntityConnection in every test with using block and so the EffortConnection is also disposed. But I updated to v1.3.9 and tested what will happen if our DB readonly tests uses a static shared connection. Unfortunately it has no performance impact to re-use the same connection between tests.

[JonathanMagnan]

Hello @fsdschmidt ,

Could you provide us an example of how you use it? We made some test on our side and got a huge impact.

Best Regards,

Jonathan

[Gabbbou]

Updated to 1.3.9, it now takes 3mins instead of 45mins. Good Job!

Thats being said, the method "clearTables" doesn't exists on "Effort.EntityConnectionFactory.CreateTransient". Seems like I don't even need it.

Thanks!

[JonathanMagnan]

Hello @Gabbbou ,

The ClearTables method exists on the EffortConnection

The Effort.EntityConnectionFactory.CreateTransient create an EntityConnection, that's why you don't see it.

You can use this code to retrieve it

var effortConnection = (Effort.Provider.EffortConnection)entitiyConnection.StoreConnection;
effortConnection.ClearTables();

I'm not sure why you get so high-performance improvement without ClearTables. Perhaps something else that has been fixed from another issue.

Best Regards,

Jonathan

[pauliusg]

Hi @JonathanMagnan,

I have tried to use effortConnection.ClearTables() but now I have other issue, I use CsvDataLoader to preload some data, after calling ClearTables that data is removed too. What can you suggest to put that data back after ClearTables?

[JonathanMagnan]

Hello @pauliusg ,

We will look at it if there is a way for us to keep seeded data.

[Gabbbou]

It might be an update from VS. In both case, performance is there so I'm happy.

[pauliusg]

@JonathanMagnan, any news on how to keep seeded data after effortConnection.ClearTables() is called?

[JonathanMagnan]

Hello @pauliusg ,

Yes, it's almost here. I just need to review and make a few change to the following pull request: #136

In short, you will be able to make a snapshot of the database after you complete the import.

Unfortunately, some personal reason makes it impossible to merge before a few days.

[pauliusg]

@JonathanMagnan, thanks for heads up. Waiting for new release :-)

[JonathanMagnan]

Hello @pauliusg ,

The v2.0.0 has been released.

We added a logic about restore point.

Example:

// Add 1 entity and create a restore point
context.EntitySimples.Add(new EntitySimple { ColumnInt = 1 });
context.SaveChanges();
connection.CreateRestorePoint();

// Add 2 entities
context.EntitySimples.Add(new EntitySimple { ColumnInt = 2 });
context.EntitySimples.Add(new EntitySimple { ColumnInt = 3 });
context.SaveChanges();

// Rollback change to the lastest restore point
connection.RollbackToRestorePoint();

Let me know if that work as expected.

Best Regards,

Jonathan

[pauliusg]

@JonathanMagnan, sadly but I am getting System.NullReferenceException when calling CreateRestorePoint method:

Initialization method BaseTestInit threw exception. System.NullReferenceException: System.NullReferenceException: Object reference not set to an instance of an object..
    at Effort.Provider.EffortConnection.CreateRestorePoint() in c:\Projects\EntityFramework-Effort-2.0.0\Main\Source\Effort.Shared\Provider\EffortConnection.cs:line 152

In line

 var uniqueDataStructureField = index.GetType().GetField("uniqueDataStructure", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);

uniqueDataStructureField is null.

index.ToString() shows this: Index`2 | COLUMN FieldWithID (Int32) | TABLE: Table<dbo_Table>

[JonathanMagnan]

Do you think you could provide an example of how you use it?

I will make some additional test later today.

Perhaps for some index or some type of index, we should not store data as they might have no data (so the uniqueDataStructure) might be null.

[JonathanMagnan]

Hello @pauliusg ,

Could you try the v2.0.0-beta3?

We successfully reproduced one case of this issue and fixed it. We now only look at the PK indexes.

Let me know if that version is working.

Best Regards,

Jonathan

[pauliusg]

Ji @JonathanMagnan,

Now CreateRestorePoint works but RollbackToRestorePoint fails:

TestCleanup method BaseTestCleanup threw exception. System.Reflection.TargetInvocationException: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object..
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Effort.Provider.EffortConnection.ClearTables(DbContext context) in c:\Projects\phoenix\EntityFramework-Effort-master\Main\Source\Effort.Shared\Provider\EffortConnection.cs:line 232
   at Effort.Provider.EffortConnection.RollbackToRestorePoint(DbContext context) in c:\Projects\phoenix\EntityFramework-Effort-master\Main\Source\Effort.Shared\Provider\EffortConnection.cs:line 193
   at Effort.Provider.EffortConnection.RollbackToRestorePoint() in c:\Projects\phoenix\EntityFramework-Effort-master\Main\Source\Effort.Shared\Provider\EffortConnection.cs:line 180
   at TestsBase.DatabaseTestBase.BaseTestCleanup() in C:\Projects\TestSolution\TestsBase\DatabaseTestBase.cs:line 75

Looks like it fails because table has column which is primary key (PK) and foreign key (FK) at the same time. We have quite many tables like this in our project. I have tried temporary separate PK and FK on one table which was causing issue and it didn't caused issues anymore but this not a solution in our project.

Also it would be good to test with composite PKs.

Another table which is causing issue is standart ASP.NET Identity Table - "AspNetUsers". It has string ID which is not autogenerated:

namespace BSS.Data.DAL
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    public class AspNetUsers
    {
        public AspNetUsers()
        {
            User = new HashSet<User>();
        }

	public string Id { get; set; }

        [StringLength(maximumLength: 256)]
        public string Email { get; set; }

        public bool EmailConfirmed { get; set; }

        public string PasswordHash { get; set; }

        public string SecurityStamp { get; set; }

        public string PhoneNumber { get; set; }

        public bool PhoneNumberConfirmed { get; set; }

        public bool TwoFactorEnabled { get; set; }

        public DateTime? LockoutEndDateUtc { get; set; }

        public bool LockoutEnabled { get; set; }

        public int AccessFailedCount { get; set; }

        [Required]
        [StringLength(maximumLength: 256)]
        public string UserName { get; set; }

        [StringLength(maximumLength: 256)]
        public string UserNameProposed { get; set; }

        [StringLength(maximumLength: 256)]
        public string EmailProposed { get; set; }

        public virtual ICollection<User> User { get; set; }
    }
}

Tried to decorate Id column with attributes Key and DatabaseGenerated(DatabaseGeneratedOption.None) but it still fails on this table.

I want to use new methods because I expect to get ferformance gain in our database tests, currently we are starting transaction on test initialize and doing rollback on test cleanup. But this is quite slow...

[JonathanMagnan]

Hello @pauliusg ,

Could you try again with the latest version?

We didn't have the time today to try your scenario but we fixed in the latest version an issue when a table had no primary key (throwing the first error).

[pauliusg]

Hi @JonathanMagnan,

another exception on 2.0.0-beta4 when calling RollbackToRestorePoint method:

TestCleanup method BaseTestCleanup threw exception. System.Reflection.TargetInvocationException: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> NMemory.Exceptions.ForeignKeyViolationException: Foreign key violation [dbo_User :: FirmID]. The key value [1] does not exists in the referenced table [dbo_Firm :: ID].. Error code: RelationError.
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Effort.Shared.Provider.EffortRestorePoint.Restore()
   at Effort.Provider.EffortConnection.RollbackToRestorePoint(DbContext context)
   at Effort.Provider.EffortConnection.RollbackToRestorePoint()
   at BSS.TestsBase.DatabaseTestBase.BaseTestCleanup() in C:\Projects\TestProject\DatabaseTestBase.cs:line 75

I want to remind that my database has pre-seeded data with CsvDataLoader and I am doing restore point for that pre-seeded data. Maybe that could cause current issue?

[JonathanMagnan]

Hello @pauliusg ,

Don't worry, we will look at it probably today.

As said, we didn't have the time to test your case on the last release, so that's normal that future issue like this one happens. We fixed case we found when testing effort with Entity Framework Extensions.

[JonathanMagnan]

Hello @pauliusg ,

The v2.0.0-beta5 has been released.

A lot of modification has been done to better support this new RestorePoint feature.

Let hope this time is the good one ;)

Best Regards,

Jonathan

[jzabroski]

I wonder if people having slowness are having slowness due to the uniquing engine internal to the ORM. This engine computes a transitive closure of an object graph, so if you're testing saving with a lot of objects, it's not the test framework that's slow, it's just your code.

[pauliusg]

Hi @JonathanMagnan,

still no luck to use new feature:

TestCleanup method BaseTestCleanup threw exception. System.Exception: System.Exception: Oops! There is an error when trying to generate the insert order. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> NMemory.Exceptions.ConstraintException: Column 'StringColumn' cannot be longer than 80 characters.. Error code: GenericError.
   at Effort.Shared.Provider.EffortRestorePoint.CreateOrderedEntities()
   at Effort.Shared.Provider.EffortRestorePoint.Restore(DbContext context)
   at Effort.Provider.EffortConnection.RollbackToRestorePoint(DbContext context)
   at Effort.Provider.EffortConnection.RollbackToRestorePoint()
   at BSS.TestsBase.DatabaseTestBase.BaseTestCleanup() in C:\Projects\Test\TestsBase\DatabaseTestBase.cs:line 75

Checked pre-seeded data table column and don't see any value which is longer than 80 characters...

[jzabroski]

@pauliusg Are you running migrations inside your tests?

[JonathanMagnan]

Thank for the additional info @pauliusg ,

We will look at it and try to reproduce it.

[pauliusg]

@jzabroskil, I create database from model with DbContext.Database.CreateIfNotExists() + CsvDataLoader pre-seeded data.

[JonathanMagnan]

Hello @pauliusg ,

We tried several scenarios but we are not able to reproduce it.

Do you think you could provide us a standalone project with this issue?

[Gabbbou]

Hi,

Updated to the last version and I am getting the Nullref error as well.

private DbConnection _dbConnection;

 _dbConnection = Effort.EntityConnectionFactory.CreateTransient("name=DBentities");
 var entityConnection = (System.Data.Entity.Core.EntityClient.EntityConnection) _dbConnection;
 var effortConnection = (Effort.Provider.EffortConnection)entityConnection.StoreConnection;
 effortConnection.ClearTables();

Some words are in french, but I think you'll understand:

" à NMemory.Tables.IdentityField1..ctor(IdentitySpecification1 identitySpecification) dans C:\Users\Jonathan\Documents\GitHub\nmemory\Main\Source\NMemory.Shared\Tables\IdentityField.cs:ligne 46
à NMemory.Tables.Table2.RestoreIdentityField() dans C:\Users\Jonathan\Documents\GitHub\nmemory\Main\Source\NMemory.Shared\Tables\Table2.cs:ligne 814"

My name is not Jonathan ^^

[JonathanMagnan]

Hello @Gabbbou ,

As asked, we need to have a standalone project that reproduces the issue. We didn't success on our side to reproduce it.

Yes, Jonathan is me ;) The path is hardcoded from the machine that build the dll (which is mine)