Entity framework and performance

Question

Hi, I am trying to develop my first web project using the entity framework, while I love the way that you can use linq instead of writing sql, I do have some severe performance issuses. I have a lot of unhandled data in a table which I would like to do a few transformations on and then insert into another table. I run through all objects and then inserts them into my new table. I need to do some small comparisons (which is why I need to insert the data into another table) but for performance tests I have removed them. The following code (which approximately 12-15 properties to set) took 21 seconds, which is quite a long time. Is it usually this slow, and what might I do wrong?

        DataLayer.MotorExtractionEntities mee = new DataLayer.MotorExtractionEntities();
        List<DataLayer.CarsBulk> carsBulkAll = ((from c in mee.CarsBulk select c).Take(100)).ToList();


        foreach (DataLayer.CarsBulk carBulk in carsBulkAll)
        {

                DataLayer.Car car = new DataLayer.Car();
                car.URL = carBulk.URL;

                car.color = carBulk.SellerCity.ToString();
                car.year = ... more properties is set this way

     mee.AddToCar(car);
    }
    mee.SaveChanges();

Answer 1

I have been experimenting with the entity framework quite a lot and I haven't seen any real performance issues.

Which row of your code is causing the big delay, have you tried debugging it and just measuring which method takes the most time?

Also, the complexity of your database structure could slow down the entity framework a bit, but not to the speed you are saying. Are there some 'infinite loops' in your DB structure? Without the DB structure it is really hard to say what's wrong.

Answer 2

Wrong post.

Answer 3

can you try the same in straight SQL?

The problem might be related to your database and not the Entity Framework. For example, if you have massive indexes and lots of check constraints, inserting can become slow.

I've also seen problems at insert with databases which had never been backed-up. The transaction log could not be reclaimed and was growing insanely, causing a single insert to take a few seconds.

Trying this in SQL directly would tell you if the problem is indeed with EF.

Answer 4

I think I solved the problem. I have been running the app locally, and the database is in another country (neighbor, but never the less). I tried to load the application to the server and run it from there, and it then only took 2 seconds to run instead of 20. I tried to transfer 1000 records which took 26 seconds, which is quite an update, though I don't know if this is the "regular" speed for saving the 1000 records to the database?

Answer 5

You cannot create batch updates using Entity Framework.

Imagine you need to update rows in a table with a SQL statement like this:

UPDATE table SET col1 = @a where col2 = @b

Using SQL this is just one roundtrip to the server. Using Entity Framework, you have (at least) one roundtrip to the server loading all the data, then you modify the rows on the client, then it will send it back row by row.

This will slow things down especially if your network connection is limited, and if you have more than just a couple of rows.

So for this kind of updates a stored procedure is still a lot more efficient.