sqlLite 单条插入和事务批量插入性能对比 

 1  public class SqlLiteInsertPerformance
 2     {
 3        //一条一条插入
 4         public static void TestPerInsertData(DataTable dt)
 5         {
 6            var dbPath= Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db", "PerformanceTest.db");
 7             var connString =string.Format(@"Data Source={0};Version=3",dbPath);
 8             var nameParam=new SQLiteParameter("Name");
 9             const string cmdTxt = "insert into tblStudent (Name) values(?)";
10             var conn = new SQLiteConnection(connString);
11             var sqliteCmd = new SQLiteCommand(cmdTxt, conn);
12             sqliteCmd.Parameters.Add(nameParam);
13             conn.Open();
14             for (int i = 0; i < dt.Rows.Count; i++)
15             {
16                 nameParam.Value = dt.Rows[i]["Name"];
17                 sqliteCmd.ExecuteNonQuery();
18             }
19             conn.Close();
20         }
21          //通过事务缓存到内存再push到db上 秒杀
22         public static void TestBulkInsertData(DataTable dt)
23         {
24             var dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db", "PerformanceTest.db");
25             var connString = string.Format(@"Data Source={0};Version=3", dbPath);
26             var nameParam = new SQLiteParameter("Name");
27             const string cmdTxt = "insert into tblStudent (Name) values(?)";
28             var conn = new SQLiteConnection(connString);
29             var sqliteCmd = new SQLiteCommand(cmdTxt, conn);
30             sqliteCmd.Parameters.Add(nameParam);
31             conn.Open();
32             using (var ts = conn.BeginTransaction())
33             {
34                 sqliteCmd.Transaction = ts;
35                 for (int i = 0; i < dt.Rows.Count; i++)
36                 {
37                     nameParam.Value = dt.Rows[i]["Name"];
38                     sqliteCmd.ExecuteNonQuery();
39                 }
40                 ts.Commit();
41             }
42             conn.Close();
43         }
44     }

 

 1  public class Tools
 2     {
 3         public static DataTable MakeDataTable()
 4         {
 5             DataTable table = new DataTable();
 6 
 7             //生成DataTable的模式(schema)
 8             table.Columns.Add("Id", Type.GetType("System.Int32"));
 9             table.Columns.Add("Name", Type.GetType("System.String"));
10 
11             //设置主键
12             table.PrimaryKey = new DataColumn[] { table.Columns["ID"] };
13             table.Columns["Id"].AutoIncrement = true;
14             table.Columns["Id"].AutoIncrementSeed = 1;
15             table.Columns["Id"].ReadOnly = true;
16             return table;
17         }
18 
19         public static void MakeData(DataTable table, int count)
20         {
21             if (table == null)
22                 return;
23 
24             if (count <= 0)
25                 return;
26 
27             DataRow row = null;
28 
29             for (int i = 1; i <= count; i++)
30             {
31                 //创建一个新的DataRow对象(生成一个新行)
32                 row = table.NewRow();
33                 row["Name"] = "Test" + i.ToString();
34                 //添加新的DataRow
35                 table.Rows.Add(row);
36             }
37         }
38     }


测试机：Intel(R) Core(TM) i5-4200U CPU @ 1.60GHz 2.3GHz

 内存：8G

系统类型：64位

测试数量：5百万

测试结果：

单条sql插入几小时。。。

事务缓存方式39秒

 

 

posted @ 2015-04-28 18:38  tiger5  阅读(388)  评论(0)    收藏  举报