Databases
StackOverflow2013
Postsdbo
POPerformance Improvement for Insert Statement
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerformance Improvement for Insert Statement
    primarykey
    data
    text
    <p>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?</p> <p>(I'm using Linq-to-Sql to insert the data into the database.)</p> <p>This is the code of the method doing the inserts:</p> <pre><code> [AcceptVerbs(HttpVerbs.Post)] public ActionResult SaveEvent(int id) { int eventID= 0; var query = from q in context.InventoryGoods where q.ParentId == id &amp;&amp; q.Action.HasValue &amp;&amp; q.ActionOn.HasValue == false select q; var stockType = from q in context.Inventory where q.Id == id select q.StockType; if (query.Count() &gt; 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(); } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJason Berkan
    1. USHallaghan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat's going on in this method: Repository.SetActionOn(i.Id, eventID);
      singulars
      1. POPerformance Improvement for Insert Statement
      1. USAmy B
    2. COI'll post it above on my main post.
      singulars
      1. POPerformance Improvement for Insert Statement
      1. USHallaghan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload