CODEWITHSUNDEEP
c#performanceentity-frameworkentity-framework-6
siddhanntarora
siddhanntarora

Reputation: 41

Entity framework performance slow

We are using EF 6.0, .NET 4.5 and using code first approach and our database has around 170 entities(tables) and the main table holding around 150,000 records On first load of the entity framework it takes around 25 seconds. I am trying to improve this time as this is too slow and as the number of records increases it becomes slower. I have tried generating native images, tried using pre generated interactive views but I couldn't achieve any significant improvements.

Can anyone please help me on this?

Thanks.

Upvotes: 4

Views: 5801

Answers (4)

lehkap
lehkap

Reputation: 56

In some cases EF does not use Query Plan Caching. For example if you use contans, any, all methods or use constants in you query. You can try NihFix.EfQueryCacheOptimizer. It convert your query expression that allow EF use cahce.

Upvotes: 0

AllmanTool
AllmanTool

Reputation: 1504

You also can circumvent this issue asynchronously "warming" your dbcontexts at application start moment.

protected void Application_Start()
 {
   // your code.
    // Warming up.
            Start(() =>
            {
                using (var dbContext = new SomeDbContext())
                { 
                    // Any request to db in current dbContext.
                    var response1 = dbContext.Addresses.Count();
                }
            });
 }

 private void Start(Action a)
 {
    a.BeginInvoke(null, null);
 }

I also recommend use such settings as: (if they fit your app)

  • dbContext.Configuration.AutoDetectChangesEnabled = false; dbContext.Configuration.LazyLoadingEnabled = false; dbContext.Configuration.ProxyCreationEnabled = false;

  • Skip validation part ( ie Database.SetInitializer<SomeDbContext>(null);)

  • Using .asNoTraking() on GET queries.

For additional information you can read:

Upvotes: 2

Shavais
Shavais

Reputation: 2586

I recently had a simple query that runs super quick in SSMS that was taking way, way too long to run using the Entity Framework in my C# program.

This page has been extremely helpful, when trouble shooting EF performance problems in general:

https://www.simple-talk.com/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/

..but in this case, nothing helped. So in the end, I did this:

        List<UpcPrintingProductModel> products = new List<UpcPrintingProductModel>();
        var sql = "select top 75 number, desc1, upccode "
                + "from MailOrderManager..STOCK s "
                + "where s.number like @puid + '%' "
        ;
        var connstring = ConfigurationManager.ConnectionStrings["MailOrderManagerContext"].ToString();
        using (var connection = new SqlConnection(connstring))
        using (var command = new SqlCommand(sql, connection)) {
            connection.Open();
            command.Parameters.AddWithValue("@puid", productNumber);
            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    var product = new UpcPrintingProductModel() {
                        ProductNumber = Convert.ToString(reader["number"]),
                        Description = Convert.ToString(reader["desc1"]),
                        Upc = Convert.ToString(reader["upccode"])
                    };
                    products.Add(product);
                }
            }
        }

(For this particular query, I just completely bypassed the EF altogether, and used the old standby: System.Data.SqlClient.)

You can wrinkle your nose in disgust; I certainly did - but it didn't actually take that long to write, and it executes almost instantly.

Upvotes: 2

Sampath
Sampath

Reputation: 65860

You can consider the Entity Framework Pre-Generated Mapping Views.You can use EF Power Tools to create pre-generate views.

Using pre-generated views moves the cost of view generation from model loading (run time) to compile time. While this improves startup performance at runtime, you will still experience the pain of view generation while you are developing. There are several additional tricks that can help reduce the cost of view generation, both at compile time and run time.

enter image description here

You can refer this for knowing more about it : Entity Framework Pre-Generated Mapping Views

You can use Caching in the Entity Framework to improve the performance of your app.

There are 3 types of caching.

1. Object caching – the ObjectStateManager built into an ObjectContext instance keeps track in memory of the objects that have been retrieved using that instance. This is also known as first-level cache.

2. Query Plan Caching - reusing the generated store command when a query is executed more than once.

3. Metadata caching - sharing the metadata for a model across different connections to the same model.

You can refer this article to read more about it : Performance Considerations for EF 6

Upvotes: 2

Related Questions