如何加快sql中多个表中的数据插入速度 [英] how to speed up data insertion in multiple table in sql

问题描述

我在sql表中有4个表，来自文本框，复选框，动态创建的文本框等形式的不同字段，数据插入正确，但我想加速数据插入，因为该表单的数据是批量的，以下是代码用于数据插入，

i have 4 tables in sql table ,from different fields in a form like textboxes,Checkboxes,dynamic created textbox, data is inserted properly,but i want to speedup data insertion because data for that forms are in bulk,following is code for data insertion,

protected void btnSubmit_Click(object sender, EventArgs e)
      {
          try
          {

              Ticket tk = new Ticket();
              tk.Sector_Id = SafeConvert.ToInt32(ddlSelectSector.SelectedValue);
              tk.Address = txtaddress.Text;
              tk.Book_ComputerNo = SafeConvert.ToInt64(txtcomputerno.Text);
              tk.Document_Id = SafeConvert.ToByte(ddldocument.SelectedValue);
              tk.DueDate = SafeConvert.ToDateTime(txtDuedate.Text);
              tk.Employee_Id = ddlRollNo.SelectedValue;
              tk.OffenceDate = SafeConvert.ToDateTime(txtoffencedate.Text);
              tk.RegistrationNo = txtRegistration.Text;
              tk.T_Number = SafeConvert.ToInt64(txttno.Text);
              tk.TicketNo = SafeConvert.ToByte(txtTicketNo.Text);
              tk.Vehicle_Impound_Id = SafeConvert.ToByte(ddlImpoundVehicle.SelectedValue);
              tk.Status = false;
              tk.IsJMC = false;
              tk.IsPaid = false;
              tk.Year = SafeConvert.ToByte(DateTime.Now.ToString("yy"));
              tk.TotalFine = 2000;
              tk.OffenderName = txtoffenderno.Text;
              _service.InsertTicket(tk);
              var lastrecord = _service.GetAllTicket().LastOrDefault();
              Int64 tkid = lastrecord.ID;

              foreach (ListItem listItem in Chkboxlst.Items)
              {
                  if (listItem.Selected)
                  {
                      TicketCrime tkcrime = new TicketCrime();
                      tkcrime.Ticket_Id = tkid;
                      var chkcrimeid = listItem.Value;
                      int chkcrime = SafeConvert.ToInt16(chkcrimeid);
                      tkcrime.OtherPunishment_Id = chkcrime;
                      tkcrime.Status = true;
                      _service.InsertTicketCrime(tkcrime);
                  }
              }
              if (ViewState["CurrentTable"] != null)
              {

                  DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
                  DataRow drCurrentRow = null;
                  if (dtCurrentTable.Rows.Count > 0)
                  {
                      drCurrentRow = dtCurrentTable.NewRow();
                      drCurrentRow["RowNumber"] = dtCurrentTable.Rows.Count + 1;

                      //add new row to DataTable
                      dtCurrentTable.Rows.Add(drCurrentRow);
                      //Store the current data to ViewState for future reference

                      ViewState["CurrentTable"] = dtCurrentTable;
                      for (int i = 0; i < dtCurrentTable.Rows.Count - 1; i++)
                      {

                          //extract the TextBox values

                          var box1 = ((TextBox)Gridview1.Rows[i].Cells[1].FindControl("txtCode")).Text;
                          var box2 = ((TextBox)Gridview1.Rows[i].Cells[2].FindControl("txtFine")).Text;
                          int box1value = SafeConvert.ToInt16(box1);
                          int box2value = SafeConvert.ToInt16(box2);

                          TicketDetails tkdetail = new TicketDetails();
                         // var chkradio = rblQualification.SelectedValue;
                          //byte chkRadio = SafeConvert.ToByte(chkradio);
                          var vehiclecategory = _service.GetAllVehicleCategory().Where(x => x.ID == SafeConvert.ToByte(rblQualification.SelectedValue)).FirstOrDefault();
                          var vehicletype = vehiclecategory.VehicleType_Id;
                          var vehiclepnalty = _service.GetAllVehiclePenaltiesFee().Where(x => x.VehicleTypeID == vehicletype).FirstOrDefault();
                          var fee = _service.GetAllVehiclePenaltiesFee().Where(x => x.VehiclePenaltyTypeID == box1value && x.VehicleTypeID == vehicletype).FirstOrDefault();
                          tkdetail.Ticket_Id = tkid;
                          tkdetail.VehicleType_Id = vehicletype;
                          tkdetail.VehiclePenaltyType_Id = vehiclepnalty.VehiclePenaltyTypeID;
                          tkdetail.VehiclePenaltiesFee_Id = fee.ID;
                          tkdetail.Status = true;
                          tkdetail.VehicleCategory_Id = vehiclecategory.ID;
                          _service.InsertTicketDetails(tkdetail);
                          _queryStatus = tkdetail.ID > 0;
                      }
                  }
              }
          }

推荐答案

没有看见我们不能具体说明InsertTicketCrime和InsertTicketDetails方法中的代码 - 我们可以告诉他们他们做了什么，更不用说他们如何做了。



但是...... 。两个建议：

1）如果你想更新两个表，使用一个Transaction。如果不这样做，并且其中一个第二个表更新失败，则只在一个表中保留孤立数据。如果一个更新失败，你应该回滚另一个 - 而事务是最好的方法。

2）如果你正在进行批量更新，那么单独调用更新方法总是会要慢 - 因为他们需要反复做很多相同的事情来设置更新。例如，创建连接，打开它，创建命令，发送它，执行它，等待它完成，关闭连接，配置命令和连接。相反，请考虑准备DataTable或DataSet并使用单个（事务）更新来同时执行所有操作 - 这可能会为您带来所需的速度。



但我们无法提出具体的建议或给你具体的代码，因为我们不知道你的系统的其余部分是如何工作的！

Without seeing the code in your InsertTicketCrime and InsertTicketDetails methods we can't be specific - we can;t tell what they do, much less how they do it.

But...two suggestions:
1) If you are trying to update two tables, use a Transaction. If you don't, and one of the second table updates fails, you are left with the orphaned data in one table only. If one update fails, you should roll back the other - and a Transaction is the best way to do that.
2) If you are doing bulk updates, then individual calls to update methods is always going to be slow - because they need to do many of the same things over and again to set up for the update. For example, create the connection, open it, create the command, send it, execute it, wait for it to complete, close the connection, dispose the command and connection. Instead, consider preparing a DataTable or DataSet and using a single (Transacted) update to do them all at the same time - that may well gain you the speed you need.

But we can't make specific recommendations or give you specific code because we don't know how the rest of your system works!

将所有数据转储到临时表中。从那里，您可以编写存储过程以适当地插入/更新。执行此操作将允许从数据库外部一个连接一个命令，因此效率更高。

Dump all your data into a staging table. From there you can write a stored procedures to insert / update as appropriate. Doing this will allow one connection one command from outside the db, and therefore is much more efficient.

如果我正确理解了这种情况，为什么不将所有修改添加到数据表然后调用存储过程进行修改并将数据表作为参数传递给过程。这样你只需要往返一次数据库并回来，这肯定会提高性能。



看看下面的例子：

- 将数据表传递给存储SQL Server 2008中的过程 [ ^ ]

- 将DataTable发送到存储过程 [ ^ ]

If I understand the situation correctly, why not add all the modifications to a data table and then call a stored procedure to make the modifications and pass the data table as a parameter to the procedure. This way you would have only one round-trip to the database and back which will certainly improve the performance.

Have a look at following examples:
- Passing a datatable to a Stored Procedure in SQL Server 2008[^]
- Sending a DataTable to a Stored Procedure[^]

这篇关于如何加快sql中多个表中的数据插入速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！