How can I get a percentage of Linq-to-SQL submitchanges?

Question

Hi, I wonder if anyone else has asked a similar question.

Basically, I have a huge tree I'm building up in RAM using Linq objects, and then I dump it all in one go using DataContext.SubmitChanges().

It works, but I can't find how to give the user a sort of visual indication of how far has the query progressed so far. If I could ultimately implement a sort of progress bar, that would be great, even if there is a minimal loss in performance.

Note that I have quite a large amount of rows to put into the DB, over 750,000 rows. I haven't timed it exactly, but it does take a long while to put them in.

EDIT : I thought I'd better give some indication of what I'm doing.

Basically, I'm building a suffix tree from the Lord of the Rings. Thus, there are a lot of Nodes, and certain Nodes have positions associated to them (Nodes that happen to be at the end of a suffix). I am building the Linq objects along these lines.

suffixTreeDB.NodeObjs.InsertOnSubmit(new NodeObj()
        {
            NodeID = 0,
            ParentID = 0,
            Path = "$"
        });

After the suffix tree has been fully generated in RAM (which only takes a few seconds), I then call suffixTreeDB.submitChanges();

What I'm wondering is if there is any faster way of doing this. Thanks!

EDIT 2 : I've did a stopwatch, and apparently it takes precisely 6 minutes for the DB to be written.

Answer 1

This isn't ideal, but you could create another thread that periodically queries the table you're populating to count the number of records that have been inserted. I'm not sure how/if this will work if you are running in a transaction though, since there could be locking/etc.

Answer 2

I suggest you divide the calls you are doing, as they are sent in separate calls to the db anyway. This will also reduce the size of the transaction (which linq does when calling submitchanges).

If you divide them in 10 blocks of 75.000, you can provide a rough estimate on a 1/10 scale.

Update 1: After re-reading your post and your new comments, I think you should take a look at SqlBulkCopy instead. If you need to improve the time of the operation, that's the way to go. Check this related question/answer: http://stackoverflow.com/questions/24200/whats-the-fastest-way-to-bulk-insert-a-lot-of-data-in-sql-server-c-client/24224#24224

Answer 3

What I really think I need is a form of Bulk-Insert, however it appears that Linq doesn't support it.