SQLite批量操作优化方案

using SQLiteBatch.Util;
using System.Collections.Concurrent;namespace SQLiteBatch
{internal class Program{static ConcurrentQueue<string> SqlQueue = new ConcurrentQueue<string>();static int ExecuteCount = 10000;static void Main(string[] args){// 初始化
            FreeSqlUtil.InitDB();// 单条执行Task.Run(() => TaskOne());// 批量添加执行Task.Run(() => TaskAdd());Task.Run(() => TaskUpdate());Console.ReadLine();}static async Task TaskOne(){var executeStopwatch = System.Diagnostics.Stopwatch.StartNew();for (int i = 0; i < ExecuteCount; i++){await FreeSqlUtil.UpdateChannel_One("Channel1.Device1.400101", i.ToString(), "Good", DateTime.Now.ToString()); }executeStopwatch.Stop(); Console.WriteLine($"单条执行, {ExecuteCount} 条 SQL, 总耗时: {executeStopwatch.ElapsedMilliseconds} ms");}static async Task TaskAdd(){for (int i = 0; i < ExecuteCount; i++){ var sql = FreeSqlUtil.UpdateChannel_Much("Channel1.Device1.400101", i.ToString(), "Good", DateTime.Now.ToString());SqlQueue.Enqueue(sql);}}static async Task TaskUpdate(){ while (true){await Task.Delay(10000);// 批量取出并执行SQLvar batchSqls = new List<string>();while (SqlQueue.TryDequeue(out string? sql)) batchSqls.Add(sql);if (batchSqls.Count > 0){var executeStopwatch = System.Diagnostics.Stopwatch.StartNew();await FreeSqlUtil.ExecuteBatchAsync(batchSqls);executeStopwatch.Stop(); Console.WriteLine($"批量执行, {batchSqls.Count} 条 SQL, 总耗时: {executeStopwatch.ElapsedMilliseconds} ms");} }}}
}

using System.Data.Common;namespace SQLiteBatch.Util
{public class FreeSqlUtil{static IFreeSql freeSql;public static void InitDB(){var ConnectionString = $"Data source={AppDomain.CurrentDomain.SetupInformation.ApplicationBase}{Path.DirectorySeparatorChar}Data.db";freeSql = new FreeSql.FreeSqlBuilder().UseConnectionString(FreeSql.DataType.Sqlite, ConnectionString).UseNoneCommandParameter(true)// 不使用参数化
                        .Build();}public static async Task<bool> UpdateChannel_One(string tag, string value, string quality, string timestamp){bool ret = false;try{ret = await freeSql.Update<Channel>().Set(it => it.OPCValue == value).Set(it => it.OPCQuality == quality).Set(it => it.OPCTime == timestamp).Where(it => it.OPCTag == tag).ExecuteAffrowsAsync() > 0;}catch (Exception ex){Console.WriteLine($"FreeSqlHelper,UpdateChannel,errmsg:{ex.Message}\r\nstacktrace:{ex.StackTrace}");}return ret;}public static string UpdateChannel_Much(string tag, string value, string quality, string timestamp){return freeSql.Update<Channel>().Set(it => it.OPCValue == value).Set(it => it.OPCQuality == quality).Set(it => it.OPCTime == timestamp).Where(it => it.OPCTag == tag).ToSql();}// 批量执行方法public static async Task ExecuteBatchAsync(List<string> lstBathSql){ using (FreeSql.Internal.ObjectPool.Object<DbConnection> conn = freeSql.Ado.MasterPool.Get()){try{using (DbTransaction transaction = conn.Value.BeginTransaction()){try{for (int i = 0; i< lstBathSql.Count; i++){ try{ await freeSql.Ado.ExecuteNonQueryAsync(transaction, lstBathSql[i]); }catch (Exception ex){ Console.WriteLine($"ExecuteBatchAsync Error(1),ex:{ex.Message},sql:{lstBathSql[i]}");}}transaction.Commit();}catch (Exception ex){transaction.Rollback();Console.WriteLine($"ExecuteBatchAsync Error(2),ex:{ex.Message}");}}}catch (Exception ex){Console.WriteLine($"ExecuteBatchAsync Error(3),ex:{ex.Message}");}}}}
}