sqlite3大量数据插入优化

用sqlite3插入十万百万行级的数据的过程优化

背景前提：客户需要导入excel文档存入数据库进行操作，每月大约在2W行左右，文件格式为xlsx。在最开始插入语句是用StringBuilder来把DataTable中的数据拼成一个超级长的sql语句，在开始执行的时候直接卡死，电脑资源跑满，磁盘读写100%(此时的数据只有1.5万行)，而且执行数据库操作的有事务操作，但依然卡死。
最开始的代码:

 StringBuilder csvSql = new StringBuilder();
 for (int i = 0; i < dtCSV.Rows.Count; i++)
            {
                csvSql.Append("insert into ALIPAY_POOL (accountNumber,businessNumber,storeOrderNumber,shopName,startTime,userAccount,incomeAmount,spendAmount,accountBalance,tradingChannel,BusinessType,note,BATCH,filename) values (");
                csvSql.Append("'").Append(dtCSV.Rows[i][0].ToString().Replace(" ","").Replace("\t","").Trim()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][1].ToString().Replace(" ","").Replace("\t","").Trim()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][2].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][3].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][4].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][5].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][6].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][7].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][8].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][9].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][10].ToString()).Append("',");
                csvSql.Append("'").Append(dtCSV.Rows[i][11].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                csvSql.Append("'").Append(batch_name).Append("',");
                csvSql.Append("'").Append(fileName).Append("');");
            }

读取文件后的dtCSV.Rows.Count的值为15680，虽然看起来不多，但是程序就是会卡死，虽然还没搞明白为什么卡死。。

修改后的代码:

StringBuilder insertSql = new StringBuilder();
insertSql.Append("insert into netBankDetails (orderNumber,documentNumber,documentType,goodsNumber,shopOrderNumber,goodsName,settlementStatus,costStartTime,costChargeTime,costSettlementTime,costOption,amountOfMoney,currency,shopReceiveOrPay,walletSettlementNote,shopNumber,JDshopNumber,brandShopNumber,shopName,note,incomeAndPayDirection,batch,fileName) ");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                insertSql.Append(" select ");
                insertSql.Append("'").Append(dt.Rows[i][0].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][1].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][2].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][3].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][4].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][5].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][6].ToString()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][7].ToString()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][8].ToString()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][9].ToString()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][10].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][11].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][12].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][13].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][14].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][15].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][16].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][17].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][18].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][19].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(dt.Rows[i][20].ToString().Replace(" ", "").Replace("\t", "").Trim()).Append("',");
                insertSql.Append("'").Append(batch_name).Append("',");
                insertSql.Append("'").Append(fileName).Append("'");

                if (i != dt.Rows.Count - 1)
                {
                    if ((i + 1) % 500 == 0)//500条执行一次插入操作   500是 select union all 连接符的个数上限
                    {
                        //count++;
                        //Console.WriteLine((i + 1)+" ---"+dt.Rows.Count);
                        insertSql.Append(";");
                        int innerRes = SqlLiteHelp.ExecuteNonQuery(SqlLiteHelp.GetConnectings(), insertSql.ToString(), CommandType.Text);//每500条执行一次
                        if (innerRes <= 0)//如果该文件插入操作中出错，则提示本文件名下的数据插入出错， 询问用户是否删除本文件下的数据
                        {
                            return false;
                        }
                        insertSql = new StringBuilder("insert into netBankDetails (orderNumber,documentNumber,documentType,goodsNumber,shopOrderNumber,goodsName,settlementStatus,costStartTime,costChargeTime,costSettlementTime,costOption,amountOfMoney,currency,shopReceiveOrPay,walletSettlementNote,shopNumber,JDshopNumber,brandShopNumber,shopName,note,incomeAndPayDirection,batch,fileName) ");
                    }
                    else //其余的语句加上 union 连接
                    {
                        insertSql.Append(" union all");
                    }
                }else
                {
                    insertSql.Append(";");
                }
                int outRes = SqlLiteHelp.ExecuteNonQuery(SqlLiteHelp.GetConnectings(), insertSql.ToString(), CommandType.Text);//将剩余的不足500条的数据执行

            if (outRes <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }

修改的地方有:
1.拼接sql语句 从原来的多个insert into 表 values 值; 变为了 insert into 表 (列名) select ‘值1’,‘值2’,… union all select ‘值1’,‘值2’,… … (此处union all 加粗 是因为 union会去重复，加上all之后插入的数据才不会被过滤)
2. 一次性执行改为每500次执行一次 (为什么必须是500呢，当时我写的1000次执行一次，程序报错说 select union term过多，然后我搜索了一下，看到有人说select union的个数最大是500 ，所以改成了500 再去执行就没问题了)。 上面的代码有一定逻辑问题，可能还不太完善，现在记录下来，如果以后发现问题再继续完善。

测试记录

18W行数据，8秒多完成
48W行数据，13秒完成

总结的地方是 在数据量大的时候不要一次性全部插入，很容易程序崩溃，我目前做的就是分批次插入，缓解数据库的压力。然后是sql的插入语句的优化， 这个select union all的写法 可以在网上搜一搜，具体为什么会快，目前还不是很理解。。
以后有机会再进行补充。
(来自程序小萌新。)