部署到客户端计算机后，Sqlite批处理插入变慢 [英] Sqlite batch insertion becoming slower after deployment to client's machine

问题描述

我有一个C＃windows应用程序，有时应用程序将用于从excel文件导入记录，我可以在我的开发机器上使用批量插入在一分钟内导入2500条记录，当我尝试正常的常规插入方法时，它也花了插入2500条记录的同一分钟，在我部署应用程序并安装在客户端的机器上后，这变得更糟，导入相同数量的记录需要两个多小时，我感觉因为我正在使用批量插入，速度应该大大优化，下面是我的代码：



我尝试过：

I have a C# windows application, sometimes the application would be used to import records from excel file, i could import 2500 records in one minute using batch insertion on my development machine, when i tried normal regular insertion approach, it also took the same one minute to insert 2500 records, this get worse after i have deployed the application and installed on a client's machine, it took more than two hours to import same number of record, i just felt since i am using bulk insertion, the speed should be greatly optimised, below is my code:

What I have tried:

public int InsertTaxRecordBatchTransaction(IEnumerable<EmployeeDetails> employeeDetails)
       {
           SQLiteConnection conn = null;
           SQLiteCommand cmd = null;
           var results = new List<int>();

           try
           {
               string insert = "INSERT INTO IncomeTax (OrganisationId,OrganisationName,StaffId,StaffName,Department,Position,NoOfMonthsWorked,TaxIdNumber,SessionId,Month,Year,BasicSalaryVALUES (@OrganisationId,@OrganisationName,@StaffId,@StaffName,@Department,@Position,@NoOfMonthsWorked,@TaxIdNumber,@SessionId,@Month,@Year,@BasicSalary);";
               using (conn = new SQLiteConnection(connstring))
               {
                   conn.Open();
                   using (var transaction = conn.BeginTransaction())
                   {
                       try
                       {
                           using (cmd = conn.CreateCommand())
                           {
                               //cmd = new SQLiteCommand(insert, conn);
                               cmd.CommandType = CommandType.Text;
                               cmd.CommandText = insert;
                               cmd.CommandTimeout = 900000000;
                               cmd.Parameters.Add("@OrganisationId", DbType.String, 50).Value = OrganisationId;
                               cmd.Parameters.Add("@OrganisationName", DbType.String, 50).Value = OrganisationName;
                               cmd.Parameters.Add("@StaffID", DbType.String, 50).Value = StaffId;
                               cmd.Parameters.Add("@StaffName", DbType.String, 50).Value = StaffName;
                               cmd.Parameters.Add("@Department", DbType.String, 50).Value = Department;
                               cmd.Parameters.Add("@Position", DbType.String, 50).Value = Position;
                               cmd.Parameters.Add("@NoOfMonthsWorked", DbType.Int16, 5).Value = NoOfMonthsWorked;
                               cmd.Parameters.Add("@TaxIdNumber", DbType.String, 5).Value = TaxIdNumber;
                               cmd.Parameters.Add("@SessionId", DbType.String, 20).Value = SessionId;
                               cmd.Parameters.Add("@Month", DbType.String, 20).Value = Month;
                               cmd.Parameters.Add("@Year", DbType.String, 20).Value = Year;
                               cmd.Parameters.Add("@BasicSalary", DbType.Decimal, 2).Value = BasicSalary;

                               foreach (var employee in employeeDetails)
                               {
                                   cmd.Parameters.Add("@OrganisationId", DbType.String, 50).Value = employee.OrganisationId;
                                   cmd.Parameters.Add("@OrganisationName", DbType.String, 50).Value = employee.OrganisationName;
                                   cmd.Parameters.Add("@StaffID", DbType.String, 50).Value = employee.StaffId;
                                   cmd.Parameters.Add("@StaffName", DbType.String, 50).Value = employee.StaffName;
                                   cmd.Parameters.Add("@Department", DbType.String, 50).Value = employee.Department;
                                   cmd.Parameters.Add("@Position", DbType.String, 50).Value = employee.Position;
                                   cmd.Parameters.Add("@NoOfMonthsWorked", DbType.Int16, 5).Value = employee.NoOfMonthsWorked;
                                   cmd.Parameters.Add("@TaxIdNumber", DbType.String, 5).Value = employee.TaxIdNumber;
                                   cmd.Parameters.Add("@SessionId", DbType.String, 20).Value = employee.SessionId;
                                   cmd.Parameters.Add("@Month", DbType.String, 20).Value = employee.Month;
                                   cmd.Parameters.Add("@Year", DbType.String, 20).Value = employee.Year;
                                   cmd.Parameters.Add("@BasicSalary", DbType.Decimal, 2).Value = employee.BasicSalary;

                                   results.Add(cmd.ExecuteNonQuery());
                               }

                           }
                           transaction.Commit();
                           DspMsg = "Tax computed successfully!";
                       }
                       catch(Exception ex)
                       {
                           transaction.Rollback();
                           DspMsg = ex.Message+ " some errors have occured, no record was saved! ";
                       }
                   }
               }
              // conn.Close();
           }
           catch (SQLiteException ex)
           {

               DspMsg = ex.Message;
           }
           return results.Sum();
       }

推荐答案

我对代码工作感到惊讶。您将新参数对象添加到您在该循环的每次迭代中回收的命令对象。因此，在您执行查询时，您有12个参数。在第二次迭代结束时24个参数，36个参数，......在2500个记录的末尾，你有30,000个参数。



你已经创建了参数对象在循环之前。太糟糕了，你不会重复使用它们。它会使代码更快。

I'm surprised the code worked at all. You're adding NEW PARAMETER OBJECTS to the command object you're recycling on every iteration of that loop. So, at the time you execute the query, you have 12 parameters. At the end of the second iteration 24 parameters, 36 parameters, ... At the end of 2500 records, you have 30,000 parameters.

You already created the parameter objects before the loop. Too bad you're not reusing them. It would have made the code a lot faster.

这篇关于部署到客户端计算机后，Sqlite批处理插入变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！