none
EF querying within a for loop getting progressively slower RRS feed

  • Question

  • Hi there, i have a EF6 context initialized and within it a for loop which could iterate tens of thousands times. each loop item queries an audit table in the context for an existing record status. Then based on that i insert/update data into the context across multiple tables.

    It starts off blazing fast and then progressively gets slower and slower as it goes on, i can only assume the context is holding onto the queried entities (which i use for read only at this point). How can i fix this performance problem ? Can i possibly clear out items in the context.Entities<T> or something like that per 100 requests?

    Any ideas appreciated.

    thanks.

    Wednesday, September 10, 2014 1:24 AM

Answers

  • Any ideas appreciated.

    http://msdn.microsoft.com/en-us/magazine/gg490349.aspx

    I don't know if you are using MS SQL Server or not.

    http://msdn.microsoft.com/en-us/library/ms173799.aspx

    It seems to me that you are doing a lot of reading, particularly on updates. You have to read the record first to materialize the object before it can be updated, which could slow things down.

    Maybe the indexing on the tables are bad.

    Maybe EF is not the ideal approach to what you are doing. And maybe you need to use straight up ADO.NET, SQL Command objects and a stored procedure.

    EF is more suited for usage for SOA solutions in a N-Tier environment. Quick-hitters, get in and get out quickly, and the life expectance is short-lived. An ORM is not suited for anything long running is the bottom, kind of like the kiss of death.

    Wednesday, September 10, 2014 3:50 AM

All replies

  • Any ideas appreciated.

    http://msdn.microsoft.com/en-us/magazine/gg490349.aspx

    I don't know if you are using MS SQL Server or not.

    http://msdn.microsoft.com/en-us/library/ms173799.aspx

    It seems to me that you are doing a lot of reading, particularly on updates. You have to read the record first to materialize the object before it can be updated, which could slow things down.

    Maybe the indexing on the tables are bad.

    Maybe EF is not the ideal approach to what you are doing. And maybe you need to use straight up ADO.NET, SQL Command objects and a stored procedure.

    EF is more suited for usage for SOA solutions in a N-Tier environment. Quick-hitters, get in and get out quickly, and the life expectance is short-lived. An ORM is not suited for anything long running is the bottom, kind of like the kiss of death.

    Wednesday, September 10, 2014 3:50 AM
  • Yes i am using SQL server for the EF connection.

    For simplicity and code maintainability i chose to stick to using EF for this, but now when i look at it i may need to start moving towards stored procs or direct SQL queries to get the performance i'm after. I think the context is just getting too big with all the querying that is happening in the for loop and after a while it starts to suffer.

    As soon as i close my form, re-open it and start again the performance is fast again and started slowing down again after a while.

    Basically my code transfers data between 2 sources, and the EF database is used to keep track of the record status (ok, failed, skipped etc) of my data transfers. i'm pretty much checking the audit table to see if the record has been previously transferred  so i know what to do with it. Once the process is done, i then take the audit object (retrieved from EF, update a few properties, then call context.SaveChanges().

    thanks.

    Wednesday, September 10, 2014 4:00 AM
  • Hello Milsnips,

    You could also try with the Entity Framework No-Tracking Queries, this may result in better performance when querying for large numbers of entities since the context does not need to trackentities anymore.

    If you use this way, please note that for updating the no tracking entity, we need to change it entity state manually as:

    db.Entry(entity).State = EntityState.Modified;

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 10, 2014 7:28 AM
    Moderator