how to speed up data insertion in multiple table in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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,

C#

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;
                      }
                  }
              }
          }

Posted 16-Aug-15 21:07pm

Sajid227

Add a Solution

Comments

Wendelius 17-Aug-15 3:24am

What is the exact code part you want to improve?

Sajid227 17-Aug-15 3:44am

1.there is may be no check box is selected or there are more than one check boxes is selected ,so i want that loop is execute only when more than one check boxes are selected or at least one check box is selected other wise it will leave it,
2.any way to optimize code that is using for dynamic text boxes:

Sinisa Hajnal 17-Aug-15 3:48am

Without changing the structure too much I'd say: create and open the connection ONCE just before the loop (or wherever you first go to the database). Don't forget THE TRANSACTION!!

I would do it differently - but you'll need to change your code. One way is to do BulkInsert/Update.

The other is to write stored procedure with the parameters you need for the insert/update and then do all needed operations in the database. You will still have to go once per item (unless you use table or XML parameter) and do a transaction, but it will save you trips to get the data objects from various tables. You just pass the parameters and update what you need.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-08-16T21:36:00

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!

chairborne82 · Answer 2 · 2015-08-17T23:27:00

Solution 2

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.

Posted 17-Aug-15 23:27pm

chairborne82

Wendelius · Answer 3 · 2015-08-20T08:15:00

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[^]

how to speed up data insertion in multiple table in sql

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0