2

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 ?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

6 Comments

So you would suggest 70k .FirstOrDefault() calls to the db ? I am doing something like that, and its taking forever... I am sure there must be a faster way, but maybe you are right, simplicity over perfomance sometimes wins.
No, I meant: look at the first XML "row", get the unique key (k) and run a (from x in table where x.unique_key == k select x).SingleOrDefault()
Also, how long does it currently take? Maybe you do need something faster. If so, avoiding EF altogether like Bas Brekelmans suggests, is probably the way to go.
Thanks for all the help. I was doing what you mentioned with the with the from a in table, but was using FirstOrDefault instead of Single.
It currently takes around 15 minutes, which isn't that bad, since the XML only changes once per day, but 70k rows in 15 mins sounds very slow !
|
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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.