向ACCESS库中插入500条数据。有以下三种方法，发现速度差距极大！

Question

向ACCESS库中插入500条数据。有以下三种方法，发现速度差距极大！
以下代码都是在一个事物中执行的，执行完毕统一提交。               for (int i = 0; i < dtSalesData.Rows.Count; i++)
                {
                    DataRow dr = dtSalesData.Rows[i];
                    com.CommandText = "INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(?,?,?,?,?,?,?,?,?)";
                    com.Parameters.Add("CHECKID", OleDbType.Integer);
                    com.Parameters["CHECKID"].Value = dr["CHECKID"].ToString();
                    com.Parameters.Add("PLU", OleDbType.Integer);
                    com.Parameters["PLU"].Value = dr["PLU"].ToString();
                    com.Parameters.Add("C_GOODSNAME", OleDbType.VarWChar);
                    com.Parameters["C_GOODSNAME"].Value = dr["C_GOODSNAME"].ToString();
                    com.Parameters.Add("E_GOODSNAME", OleDbType.VarWChar);
                    com.Parameters["E_GOODSNAME"].Value = dr["E_GOODSNAME"].ToString();
                    com.Parameters.Add("SPECNO", OleDbType.Integer);
                    com.Parameters["SPECNO"].Value = dr["SPECNO"].ToString();
                    com.Parameters.Add("C_SPECNAME", OleDbType.VarWChar);
                    com.Parameters["C_SPECNAME"].Value = dr["C_SPECNAME"].ToString();
                    com.Parameters.Add("E_SPECNAME", OleDbType.VarWChar);
                    com.Parameters["E_SPECNAME"].Value = dr["E_SPECNAME"].ToString();
                    com.Parameters.Add("MONEY", OleDbType.Double);
                    com.Parameters["MONEY"].Value = dr["MONEY"].ToString();
                    com.Parameters.Add("STALL", OleDbType.VarWChar);
                    com.Parameters["STALL"].Value = dr["STALL"].ToString();
m_db.ExecuteNonQuery(com);
                }

Answer 1

                for (int i = 0; i < dtSalesData.Rows.Count; i++)
                {
                    DataRow dr = dtSalesData.Rows[i];                    com.CommandText = "INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(?,?,?,?,?,?,?,?,?)";
                    OleDbParameter[] par = {new OleDbParameter("CHECKID", dr["CHECKID"]),
                        new OleDbParameter("PLU", dr["PLU"]),
                        new OleDbParameter("C_GOODSNAME", dr["C_GOODSNAME"]),
                        new OleDbParameter("E_GOODSNAME", dr["E_GOODSNAME"]),
                        new OleDbParameter("SPECNO", dr["SPECNO"]),
                        new OleDbParameter("C_SPECNAME", dr["C_SPECNAME"]),
                        new OleDbParameter("E_SPECNAME", dr["E_SPECNAME"]),
                        new OleDbParameter("MONEY", dr["CHECKID"]),
                        new OleDbParameter("STALL", dr["CHECKID"])};
                    com.Parameters.AddRange(par);                    m_db.ExecuteNonQuery(com);
                }

Answer 2

for (int i = 0; i < dtSalesData.Rows.Count; i++)
                {
                    DataRow dr = dtSalesData.Rows[i];                    StringBuilder SQL = new StringBuilder("INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(");
                    SQL.Append(dr["CHECKID"].ToString());
                    SQL.Append(",");
                    SQL.Append(dr["PLU"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["C_GOODSNAME"].ToString());
                    SQL.Append("','");
                    SQL.Append(dr["E_GOODSNAME"].ToString());
                    SQL.Append("',");
                    SQL.Append(dr["SPECNO"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["C_SPECNAME"].ToString());
                    SQL.Append("','");
                    SQL.Append(dr["E_SPECNAME"].ToString());
                    SQL.Append("',");
                    SQL.Append(dr["MONEY"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["STALL"].ToString());
                    SQL.Append("')");
                    com.CommandText = SQL.ToString();
                    m_db.ExecuteNonQuery(com);
                }

Answer 3

第1，第2种方法速度差不多。耗时大约1分半钟。
第3种方法最快，耗时大约1秒钟。
CPU双核2G，都是在同一台电脑上。为啥差距这么大？前两种方法写的不对吗？

Answer 4

区别new OleDbParameter的时间

Answer 5

try this                    com.CommandText = "INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(?,?,?,?,?,?,?,?,?)";
                    com.Parameters.Add("CHECKID", OleDbType.Integer);
                    com.Parameters.Add("PLU", OleDbType.Integer);
                    com.Parameters.Add("C_GOODSNAME", OleDbType.VarWChar);
                    com.Parameters.Add("E_GOODSNAME", OleDbType.VarWChar);
                    com.Parameters.Add("SPECNO", OleDbType.Integer);
                    com.Parameters.Add("C_SPECNAME", OleDbType.VarWChar);
                    com.Parameters.Add("E_SPECNAME", OleDbType.VarWChar);
                    com.Parameters.Add("MONEY", OleDbType.Double);
                    com.Parameters.Add("STALL", OleDbType.VarWChar);
    for (int i = 0; i < dtSalesData.Rows.Count; i++)
                {
                    DataRow dr = dtSalesData.Rows[i];
                    com.Parameters["CHECKID"].Value = dr["CHECKID"];
                    com.Parameters["PLU"].Value = dr["PLU"];
                    com.Parameters["C_GOODSNAME"].Value = dr["C_GOODSNAME"];
                    com.Parameters["E_GOODSNAME"].Value = dr["E_GOODSNAME"];
                    com.Parameters["SPECNO"].Value = dr["SPECNO"];
                    com.Parameters["C_SPECNAME"].Value = dr["C_SPECNAME"];
                    com.Parameters["E_SPECNAME"].Value = dr["E_SPECNAME"];
                    com.Parameters["MONEY"].Value = dr["MONEY"];
                    com.Parameters["STALL"].Value = dr["STALL"];
m_db.ExecuteNonQuery(com);
                }

Answer 6

估计是这样，提取了一部分代码出来。
另外赋值时com.Parameters["CHECKID"]是一个线性搜索，你字段太多效率较差。你可以改成com.Parameters[0]之类的写法，会更快一些

Answer 7

Append快new OleDbParameter   com.Parameters.Add 慢

Answer 8

StringBuilder SQL = new StringBuilder("INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(");for (int i = 0; i < dtSalesData.Rows.Count; i++)
                {
                    DataRow dr = dtSalesData.Rows[i];                    
                    SQL.Append(dr["CHECKID"].ToString());
                    SQL.Append(",");
                    SQL.Append(dr["PLU"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["C_GOODSNAME"].ToString());
                    SQL.Append("','");
                    SQL.Append(dr["E_GOODSNAME"].ToString());
                    SQL.Append("',");
                    SQL.Append(dr["SPECNO"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["C_SPECNAME"].ToString());
                    SQL.Append("','");
                    SQL.Append(dr["E_SPECNAME"].ToString());
                    SQL.Append("',");
                    SQL.Append(dr["MONEY"].ToString());
                    SQL.Append(",'");
                    SQL.Append(dr["STALL"].ToString());
                    SQL.Append("')");
                    com.CommandText = SQL.ToString();
                    m_db.ExecuteNonQuery(com);
                }这样会更快点,固定的东西就不要放进for里面,

Answer 9

第三种只有一次连接数据库的操作（只有一条很长的SQL），前两种每执行一条SQL都要连接一次数据库，这方面开销大了。

Answer 10

不是啊。这3种方法都是只连接一次数据库。执行完了统一提交。
不一样的部分只在for循环中。

Answer 11

你写的不对，不用每次加参数
com.CommandText = "INSERT INTO salesData([checkid],[plu],[c_goodsname],[e_goodsname],[specno],[c_specname],[e_specname],[money],[stall]) values(?,?,?,?,?,?,?,?,?)";                    
com.Parameters.Add("CHECKID", OleDbType.Integer);
com.Parameters.Add("PLU", OleDbType.Integer);
com.Parameters.Add("C_GOODSNAME", OleDbType.VarWChar);
com.Parameters.Add("E_GOODSNAME", OleDbType.VarWChar);
com.Parameters.Add("SPECNO", OleDbType.Integer);
com.Parameters.Add("C_SPECNAME", OleDbType.VarWChar);
com.Parameters.Add("E_SPECNAME", OleDbType.VarWChar);
com.Parameters.Add("MONEY", OleDbType.Double);
com.Parameters.Add("STALL", OleDbType.VarWChar);
com.Prepare();for (int i = 0; i < dtSalesData.Rows.Count; i++)
{
    DataRow dr = dtSalesData.Rows[i];
    
    com.Parameters["CHECKID"].Value = dr["CHECKID"].ToString();                    
    com.Parameters["PLU"].Value = dr["PLU"].ToString();                    
    com.Parameters["C_GOODSNAME"].Value = dr["C_GOODSNAME"].ToString();                    
    com.Parameters["E_GOODSNAME"].Value = dr["E_GOODSNAME"].ToString();                    
    com.Parameters["SPECNO"].Value = dr["SPECNO"].ToString();                    
    com.Parameters["C_SPECNAME"].Value = dr["C_SPECNAME"].ToString();                    
    com.Parameters["E_SPECNAME"].Value = dr["E_SPECNAME"].ToString();                    
    com.Parameters["MONEY"].Value = dr["MONEY"].ToString();                    
    com.Parameters["STALL"].Value = dr["STALL"].ToString();
com.ExecuteNonQuery();
}不知道Access支不支持Prepare();
我用的SQLServer，我试过500条记录也就几秒的时间吧，大概10秒吧

Answer 12

也可能这种直接的SQL更适合Access数据库

Answer 13

第三种方法执行了一次大的SQL,
前两种方法执行了100次小的SQL

Answer 14

你把500条联成一句，再execute,可能还要快。

Answer 15

这个不行呀。SQL的值在第二次循环时已经不是初始值了。