Entity Framework and loading all database entries vs loading each as required

Question

I have a scenario where I need to synchronize a database table with a list (XML) from an external system.

I am using EF but am not sure which would be the best way to achieve this in terms of performance.

There are 2 ways to do this as I see, but neither seem to be efficient to me.

1. Call Db each time -Read each entry from the XML -Try and retrieve the entry from the list -If no entry found, add the entry -If found , update timestamp -At end of loop, delete all entries with older timestamp.

2. Load All Objects and work in memory

   • Read all EF objects into a list.
   • Delete all EF objects
   • Add item for each item in the XML
   • Save Changes to Db.

The lists are not that long, estimating around 70k rows. I don't want to clear the db table before inserting the new rows, as this table is a source for data from a webservice, and I don't want to lock the table while its possible to query it.

If I was doing this in T-SQL i would most likely insert the rows into a temp table, and join to find missing and deleted entries, but I have no idea how the best way to handle this in Entity Framework would be.

Any suggestions / ideas ?

Answer 1

It depends. It's ok to use EF if there'll be not many changes (say less than hundreds). Otherwise, need bulk insert into DB and merge rows inside database.

Answer 2

The general problem with Entity Framework is that, when changing data, it will fire a query for each changed record anyway, regardless of lazy or eager loading. So by nature, it will be extremely slow (think of factor 1000+).

My suggestion is to use a stored procedure with a table valued parameter and ignore Entity Framework all together. You could use a merge statement.

70k rows is not much, but 70k insert/update/delete statements is always going to be very slow.

You could test it and see if the performance is managable, but intuition says entity framework is not the way to go.

Answer 3

I would iterate over the elements in the XML and update the corresponding row in the DB one at a time. I guess that's what you meant with your first option? As long as you have a good query plan to select each row, that should be pretty efficient. Like you said, 70k rows isn't that much so you are better off keeping the code straightforward rather than doing something less readable for a little more speed.