利用petapoco批量插入SQLite效率对比

插入数据条数为3000条

1. 不开启事务，使用petapoco Sql类组装sql语句和参数，一次性插入。

   DateTime stime = DateTime.Now;
               Sql sql = new Sql();
               for (int i = 1; i < 3001; i++)
               {
                  //sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(1," + i + ",2,10000,10000,1000,3," + i + ",45);");
                  sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(@0,@1,@2,@3,@4,@5,@6,@7,@8);",1,i,2,10000,10000,1000,3,i,45);
               }
               Database db = new Database(@"Data Source=D:\temp\jsrj.db;Version=3;", "System.Data.Sqlite");
               //db.BeginTransaction();
               db.Execute(sql);
               //db.CompleteTransaction();
               var times = DateTime.Now.Subtract(stime).TotalSeconds;
               MessageBox.Show("耗时：" + times.ToString());

   很慢，超过300秒还没结束。

2. 开启事务，使用Sql类组装sql语句和参数。一次执行。

   DateTime stime = DateTime.Now;
               Sql sql = new Sql();
               for (int i = 1; i < 3001; i++)
               {
                  //sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(1," + i + ",2,10000,10000,1000,3," + i + ",45);");
                  sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(@0,@1,@2,@3,@4,@5,@6,@7,@8);",1,i,2,10000,10000,1000,3,i,45);
               }
               Database db = new Database(@"Data Source=D:\temp\jsrj.db;Version=3;", "System.Data.Sqlite");
               db.BeginTransaction();
               db.Execute(sql);
               db.CompleteTransaction();
               var times = DateTime.Now.Subtract(stime).TotalSeconds;
               MessageBox.Show("耗时：" + times.ToString());

   很慢，160秒钟。

3. 开启事务。直接用Sql类时，直接把sql语句值拼装好，不使用带@参数拼装。一次执行。

   DateTime stime = DateTime.Now;
               Sql sql = new Sql();
               for (int i = 1; i < 3001; i++)
               {
                   sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(1," + i + ",2,10000,10000,1000,3," + i + ",45);");
                   //sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(@0,@1,@2,@3,@4,@5,@6,@7,@8);",1,i,2,10000,10000,1000,3,i,45);
               }
               Database db = new Database(@"Data Source=D:\temp\jsrj.db;Version=3;", "System.Data.Sqlite");
               db.BeginTransaction();
               db.Execute(sql);
               db.CompleteTransaction();
               var times = DateTime.Now.Subtract(stime).TotalSeconds;
               MessageBox.Show("耗时：" + times.ToString());

   用时3.7秒。提升了数倍。

4. 开启事务。直接把sql语句值拼装好，拼一条，执行一次，不使用带@参数拼装。最后提交一次。

   Database db = new Database(@"Data Source=D:\temp\jsrj.db;Version=3;", "System.Data.Sqlite");
               DateTime stime = DateTime.Now;
               
               db.BeginTransaction();
               for (int i = 1; i < 3001; i++)
               {
                   Sql sql = new Sql("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(1," + i + ",2,10000,10000,1000,3," + i + ",45);");
                   //sql.Append("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(@0,@1,@2,@3,@4,@5,@6,@7,@8);",1,i,2,10000,10000,1000,3,i,45);
                   db.Execute(sql);
               }                        
               
               db.CompleteTransaction();
               var times = DateTime.Now.Subtract(stime).TotalSeconds;
               MessageBox.Show("耗时：" + times.ToString());

   用时0.2秒。这样就很快了。

5. 开启事务。用Sql类拼装，用带@参数拼装。拼一条执行一条。最后一次性提交。

   Database db = new Database(@"Data Source=D:\temp\jsrj.db;Version=3;", "System.Data.Sqlite");
               DateTime stime = DateTime.Now;
               
               db.BeginTransaction();
               for (int i = 1; i < 3001; i++)
               {
                   //Sql sql = new Sql("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(1," + i + ",2,10000,10000,1000,3," + i + ",45);");
                   Sql sql = new Sql("insert into Data(batch_id,point_no,type,x,y,z,freq,rho,phase) values(@0,@1,@2,@3,@4,@5,@6,@7,@8);",1,i,2,10000,10000,1000,3,i,45);
                   db.Execute(sql);
               }                        
               
               db.CompleteTransaction();
               var times = DateTime.Now.Subtract(stime).TotalSeconds;
               MessageBox.Show("耗时：" + times.ToString());

   用时0.37秒。也还可以。

结论：以上试验的5种方式，使用事务，直接拼sql，拼一条执行一次，最后一次性提交方式最快。