ansaurus

Question

Performance Improvement for Insert Statement

Answer 1

A:

What DAL do you use EF, L2S, ADO.net or something else? Insert shouldn't take so much time to complete. You can insert them to local cache, and submit changes later.

Wyvern 2010-07-21 11:01:04

We're using ADO.NET.

Hallaghan 2010-07-21 11:05:31

After trying to insert 8500 lines, it took the system 30 minutes to complete the operation.

Hallaghan 2010-07-21 11:16:52

OK, got it. Look at this pattern below:context.GoodsEvent.InsertOnSubmit(ge);context.SubmitChanges();Just move "context.SubmitChanges();" statement out of scope even out of foreach loop.SubmitChanges is a time consuming operation, it sync local cache and remote Sql DB data status. If you insert 8500 lines, it will sync 8500 times right? But after move SubmitChanges() out of loop, it only sync one time.

Wyvern 2010-07-21 11:36:05

@Wyvern- I'm testing with Context.SubmitChanges outside the loop right now.

Hallaghan 2010-07-21 11:43:25

And it completed with the following error:Exception Details: System.Data.SqlClient.SqlException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.Source Error: Line 729: }Line 730: }Line 731: context.SubmitChanges();Line 732: }Line 733: else

Hallaghan 2010-07-21 11:44:23

The optimized pattern should be:using (var scope = new TransactionScope()){foreach (var i in query){...context.GoodsEvent.InsertOnSubmit(ge);...}context.SubmitChanges();//Add exception handling here, you can rollback operation.scope.Complete();}

Wyvern 2010-07-21 12:08:18

I will try it that way, should have known better that there was something wrong with my code. Thanks for your support!

Hallaghan 2010-07-21 12:11:26

Answer 2

A:

Not C# programmer, but i suggest "batch update".

foret 2010-07-21 11:02:23

Answer 3

+1 A:

Linq-to-sql really wasn't designed for inserting that many records to the database in one batch. It will do it insert statement by insert statement which is really slow. I'd recommend that anywhere where you know you'll need to support this many inserts that you use the SqlBulkCopy object instead of your Linq-to-sql classes. You could even still use your same L2S classes if you need them for object validation, but then just dump them into a DataTable in 1000 row chunks and let SqlBulkCopy do your actual inserts. You could even google L2S and SqlBulkCopy and see what's out there as far as extension methods or other integration. You aren't the first one to run into this problem.

mattmc3 2010-07-21 11:24:40

+1 for `SqlBulkCopy`

abatishchev 2010-07-21 11:27:16

Have you looked at the code of my method above? Do you think I could improve it to make it any faster? I'm wondering whether I could run those "context.SubmitChanges();" just in the end of my switch or if I need to run them as they're ran now. What do you think?

Hallaghan 2010-07-21 11:30:00

It wasn't there when I posted my answer, but at first glance there's a lot of duplication of code. This might be ripe for a refactor **before** optimizing the insert performance.

mattmc3 2010-07-21 12:10:20

Answer 4

+1 A:

Stop using var so much.

This runs the query twice (observe in the sqlprofiler).

if (query.Count() > 0) 
{ 
  foreach (var i in query)

Use this to avoid running the query multiple times.

List<InventoryGoods> rows = query.ToList();

Your code has much repetition. Also, you're trying to do too little in the context in each Submit. You're controlling transaction scope for no reason.

foreach(InventoryGood i in rows)
{
  InventoryGoodsEvent ige = new InventoryGoodsEvent()
    //this will attach ige to the object graph tracked by context
    // which is sufficient to insert ige when submitchanges is called.
  ige.InventoryGood = i;

  GoodsEvent ge = GetGoodsEvent(i); //all that conditional logic in there.
    //this will attach ge to the object graph tracked by context
    // which will both insert ge and update ige with ge's id when submitchanges is called.
  ige.GoodsEvent = ge;

  i.ActionOn = DateTime.Now;
    //to submit each row, uncomment this.  
  //context.SubmitChanges();
}
 //to submit all rows at once, use this.
context.SubmitChanges();

If InventoryGoodEgvents doesn't have those relational properties, go into the designer and add associations to create them.

Once you have such code, then you get to decide what's a good amount of change to do in one transaction. I like to insert ~100 records per transaction. If you use 1 record per transaction, there's a high overhead of creating each transaction. If you use 1,000,000 rows per transaction, there's the high overhead of a long running transaction.

This stuff is hard to learn, but keep at it.

One more thing: Bulk insert isn't going to work with that many-to-many table.

David B 2010-07-21 12:11:52

<subjective-comment-alert>There's nothing wrong with how var was used here. It's in conjunction with Linq queries, which everyone knows return IEnumerable or whatever you selected, and he's also used in in conjunction with the new keyword - which is much less redundant than the rediculous `TransactionScope scope = new TransactionScope();`. Var can be overused, but here it was certainly appropriate.</subjective-comment-alert>

mattmc3 2010-07-21 17:02:11

There are certain declarations where var is required. Using var beyond that is a matter of style (as you say, subjective). Your chosen case saves a mere 13 characters. Perhaps if the line wasn't optimized for length, it wouldn't have been used so many times, in the end saving many many more characters. In the case of the comprehension query syntax result assignment, as you say "or whatever". "Or whatever" is the problem.

David B 2010-07-22 02:18:16

ansaurus

tags:

views:

answers:

Performance Improvement for Insert Statement

related questions