Hallaghan
Reputation: 1941
Performance Improvement for Insert Statement
On my ASP.NET MVC application I'm running a couple of inserts at once that can insert 10000 or more lines and update a few others. This process is taking a long time but I can't escape the insert because that's exactly what I was asked to do. Right now I'm running Sql Server Profiler and it takes almost 20 minutes to insert this bunch of rows. How could I possibly improve the performance of this action?
(I'm using Linq-to-Sql to insert the data into the database.)
This is the code of the method doing the inserts:
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult SaveEvent(int id)
{
int eventID= 0;
var query = from q in context.InventoryGoods
where q.ParentId == id && q.Action.HasValue && q.ActionOn.HasValue == false
select q;
var stockType = from q in context.Inventory
where q.Id == id
select q.StockType;
if (query.Count() > 0)
{
foreach (var i in query)
{
switch (i.Action.Value)
{
case (int)InventoryGoodsActionEnum.AdjustLocation:
Guid guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "LO",
Lid = i.LidObtained,
Comments = "Inventário "+i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
break;
case (int)InventoryGoodsActionEnum.AdjustQuantity:
if (!i.QuantityObtained.HasValue)
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "AQ",
Quantity = (short)(i.QuantityExpected * -1),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
else if ((i.QuantityObtained - (i.QuantityExpected.HasValue ? i.QuantityExpected : 0) != 0))
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "AQ",
Quantity = (short)(i.QuantityObtained.Value - (i.QuantityExpected.HasValue ? i.QuantityExpected : 0)),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
break;
case (int)InventoryGoodsActionEnum.AdjustQuantityLocation:
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "LO",
Lid = i.LidExpected,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
if (!i.QuantityObtained.HasValue)
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityExpected * -1),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
else if ((i.QuantityObtained - (i.QuantityExpected.HasValue ? i.QuantityExpected : 0) != 0))
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityObtained.Value - (i.QuantityExpected.HasValue ? i.QuantityExpected : 0)),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
break;
case (int)InventoryGoodsActionEnum.AdjustStockType:
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "ST",
StockType = stockType.First().Value,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
break;
case (int)InventoryGoodsActionEnum.AdjustLocationStockType:
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "ST",
StockType = stockType.First().Value,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "LO",
Lid = i.LidExpected,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
break;
case (int)InventoryGoodsActionEnum.AdjustQuantityStockType:
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "ST",
StockType = stockType.First().Value,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
if (!i.QuantityObtained.HasValue)
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityExpected * -1),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
else if ((i.QuantityObtained - i.QuantityExpected != 0))
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityObtained.Value - i.QuantityExpected),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
break;
case (int)InventoryGoodsActionEnum.AdjustQuantityLocationStockType:
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now,
EventOn = DateTime.Now,
Type = "ST",
StockType = stockType.First().Value,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
if (!i.QuantityObtained.HasValue)
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityExpected * -1),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
else if ((i.QuantityObtained - i.QuantityExpected != 0))
{
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(1),
EventOn = DateTime.Now.AddSeconds(1),
Type = "AQ",
Quantity = (short)(i.QuantityObtained.Value - i.QuantityExpected),
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
}
guid = Guid.NewGuid();
using (var scope = new TransactionScope())
{
GoodsEvent ge = new GoodsEvent()
{
Gid = i.Gid,
Guid = guid,
CreatedOn = DateTime.Now.AddSeconds(2),
EventOn = DateTime.Now.AddSeconds(2),
Type = "LO",
Lid = i.LidExpected,
Comments = "Inventário " + i.ParentId,
UserId = GetUserId(),
};
context.GoodsEvent.InsertOnSubmit(ge);
context.SubmitChanges();
eventID = ge.Id;
Repository.SetActionOn(i.Id, eventID);
scope.Complete();
}
break;
}
}
}
else
{
var lista = from q in context.InventoryGoods
where q.ParentId == id
select q;
Repository.EvaluateActions(lista.ToList());
SaveEvent(id);
}
using (var scope = new TransactionScope())
{
var thisInventory = from i in context.Inventory
where i.Id == id
select i;
thisInventory.First().State = (int)InventoryStateEnum.Verified;
context.SubmitChanges();
scope.Complete();
}
Status.Info(string.Format("Acções aplicadas com sucesso."));
return RedirectToAction("Details", new { id });
}
public void SetActionOn(int id, int eventID)
{
var InventoryGoods = from i in context.InventoryGoods
where i.Id == id
select i;
using (var scope = new TransactionScope())
{
InventoryGoods.First().ActionOn = DateTime.Now;
InventoryGoodsEvents ige = new InventoryGoodsEvents
{
EventId = eventID,
InventoryGood = InventoryGoods.First().Id,
};
context.InventoryGoodsEvents.InsertOnSubmit(ige);
scope.Complete();
}
}
Upvotes: 0
Views: 362
Answers (3)
Amy B
Reputation: 110091
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.
Upvotes: 1
mattmc3
Reputation: 18325
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.
Upvotes: 1
Wyvern
Reputation: 1
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.
Upvotes: 0
Related Questions
- Improve insert performance for a C# application into SQL database
- How to do faster insert query in c#?
- Efficient insert statement
- Inserting to SQL performance issues
- How to do efficient inserts?
- Speed up LINQ inserts
- LINQ-to-SQL performance issue for mass inserts
- Very slow insert process using Linq to Sql
- Faster SQL Inserts?
- SQL Insert Performance Improvement