Entity framework performance slow

c# entity-framework entity-framework-6 performance

Question

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.

1
3
11/10/2016 1:16:22 PM

Popular Answer

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.

1
2/17/2017 2:02:23 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow