New Posts New Posts RSS Feed: Batch (> 10.000 records) save
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Batch (> 10.000 records) save
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
detritus View Drop Down
Newbie
Newbie


Joined: 04-Jul-2010
Posts: 3 		Post Options Post Options   Quote detritus Quote  Post ReplyReply Direct Link To This Post Topic: Batch (> 10.000 records) save
    Posted: 25-Aug-2010 at 3:29am
Hi,

First let me state that I'm new to DevForce (also to EF) and having difficulties. I've read neary all the documentation (even the subjects I don't need rigth now because of the insight they gave me) and forum topics that I can search and locate. There are few topics about batch operations like saving/processing large number of records and I could not get what I need from these.

Here is my sample senario (nothing is async, it's a simple console app with DevForce 6.0.3.1):

I've got 4 tables all linked like:
T1 -> T2 -> T3 -> T4

Saving records for T1, T2, and T3 can be done in usual way because they contain relatively low number of records but the record number for T4 is usually more than 10.000, and it takes around 3 minutes to save on my notebook.

You can think of T4 as always insert, because whatever changes in other tables all of the records in T4 is affected and it's easier to delete them all and insert instead of > 10.000 update statements (This is of course mostly incorrect except for what I want to do below).

So, can I make DevForce to let me handle saving of all the records in T4 manually (I'm thinking using SP with a json like param input for each 1.000 records and posting this in Service Broker to let to app continue without waiting for SP to parse this json structure - because only single field has importance for a value and all others are keys in T4, thus could be represented fairly short in string format).

I've tried couple of things with EntityServerSaveInterceptor (with ExecuteSave override) but could not make it work. It always fails with an exception  like "The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: A referential integrity constraint violation occurred...". The problem seems to be with the store generated identity field value and tried some actions for this but still does not work.

I've uploaded a sample project to the file area:
http://www.ideablade.com/forum/uploads/835/DFBatchTest.zip

I'll appreciate any help,
Thanks in advance,

Sinan
Back to Top
detritus View Drop Down
Newbie
Newbie


Joined: 04-Jul-2010
Posts: 3 		Post Options Post Options   Quote detritus Quote  Post ReplyReply Direct Link To This Post Posted: 26-Aug-2010 at 6:03am
No takers?

Well, let me put more detail then, may be it'll attract more attention:

In short what I'm trying to do is to handle saving of a table (T4) manually, because of it's huge number of records. As I said before, I want to do that without fully skipping DF/EF in order to keep Business logic contained in a single place. Here is the data model:

DFBatchTest DataModel


I've tried couple of things with EntityServerSaveInterceptor override:
In ExecuteSave override, tried to get a list of the items I want to save manually:
Snippet
1) _SkippedItems = em.FindEntities<SalesTargetItemTrtryDist>(EntityState.Added).ToList();
and removed them from the EM:Snippet
2a) _SkippedItems.ForEach(i => i.EntityAspect.RemoveFromManager(true));

DB save OK but ObjectContext update failed. Tried 1) with this:
Snippet
2b) _SkippedItems.ForEach(i => i.EntityAspect.Delete()); -- These are EntityState.Added entities, so no db delete will occur.
Same exception with 2a).

The idea is to remove/detach entities from the leaf level table, insert them into the db in another way and attach them again to EM.
But I couldn't pass base.ExecuteSave() call without an exception.


Any ideas?
Back to Top
ting View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 27-Mar-2009
Location: San Francisco
Posts: 427 		Post Options Post Options   Quote ting Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2010 at 4:41pm
What happens if you call EntityManager.SaveChanges() and pass in the T1, T2, and T3, but not T4?  In other words, instead of removing them in the EntityServerSaveInterceptor, don't submit them for the save in the first place.  You could then call InvokeServerMethod() to handle the bulk operation separately.
 
Also, DevForce 6.0.5 will have much faster AddToManager/AddEntity performance when creating large numbers of new records.  It's probably not the dominant effect in your code, but I thought it worth mentioning.
 
Separately, as you noticed, ORM systems (including DevForce) tend not to be a great solution for bulk updates/inserts due to the object overhead.  The path you are on seems to make sense as soon as we get it to work.
 
Back to Top
detritus View Drop Down
Newbie
Newbie


Joined: 04-Jul-2010
Posts: 3 		Post Options Post Options   Quote detritus Quote  Post ReplyReply Direct Link To This Post Posted: 27-Aug-2010 at 5:26pm
First thanks for the help.

Now you mention, I remember seeing this overload on some other post but never thought using it for this purpose and it works GREAT, actually it works noticably FASTER than what I was trying to do presumably I was making EM to spend too much time checking and preping the huge number of entities that I won't save.

Yes, you're right about the bulk process using ORMs and also read that in other posts but let just say this is not all bulk insert, master entities actually have lots of logic and rules on them and just because of this single table I didn't want to move to whole external batch process methods if I'm not have to. I think with your solution I can make this "partial batch process" work.

Thanks again,
Sinan
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down