SQLite/C#连接池和准备语句的困惑

Question

我最近花了一些时间阅读关于数据库和特别是SQLite的最佳实践。在阅读过程中，我发现我做了很多不应该做的事情，并且在试图修复这些问题时，有些使用SQLite的ADO实现的细节让我感到困惑。

我的困惑主要来自准备好的语句和连接池。

在阅读 http://msdn.microsoft.com/en-us/library/ms971481.aspx 时，我发现连接应该仅在事务中打开。完成事务后，连接应关闭。我并没有完全掌握这是为什么，但我一直在假设作者们比我更明白。我知道当一个连接被关闭时，并不意味着它已经被真正关闭。它只是被放回连接池中。

现在为了改进查询和插入，我读到了有关使用准备好的语句的内容。 In SQLite, do prepared statements really improve performance? 和 http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html 似乎都表明，当执行将被多次执行的查询时，准备好的语句是正确的选择。我也读到了，准备好的语句是特定于连接的，一旦连接关闭，准备好的语句就会丢失。

我的困惑在于，如果我打开和关闭连接（这可能或可能不意味着由于线程池而关闭连接），那么我真正从准备好的语句中获得了多少好处？我可以理解，如果我需要保存1000个对象在单个事务中，准备好的语句可以帮助很多。但我不认为我会从在一个事务中保存一个对象中获得好处，因为一旦我关闭连接，从第一个对象生成的准备好的语句就会丢失。这是一个正确的陈述吗？

我的困惑进一步加深了，因为我认为准备好的语句与我的SQLiteCommand对象的范围相关联。

如果我创建一个代表我将经常执行的查询的SQLiteCommand，那么我需要将该SQLiteCommand保留在内存中，以使准备好的语句保持活动状态吗？

如果我创建一个具有相同SQLite语句的新SQLiteCommand，是否会认识到新的SQLiteCommand与先前的SQLiteCommand相同，从而可以使用准备好的语句？

如果我在打开并关闭不同事务的连接时，在内存中保留一个SQLiteCommand并更改它的参数和连接，那么我是否在不同连接之间保持了一个准备好的语句？

我现在可能想太多了，但我希望你能帮助我更好地理解这些要点，以便我可以从中获得最大的效益。

- Lux782

---

我对SQLite不是很熟悉，但是关于为什么应该尽快关闭连接，你已经接近回答了：底层物理连接没有关闭，你只是将连接返回到池中。这意味着它可以被其他线程使用。显然，如果你保持连接但实际上不使用它，那么这将限制最大可能的利用率。（同样明显的是：如果你在单线程上下文中，连接池并没有太多意义，虽然它不太可能对你造成伤害。） - The Dag

Answer 1

需要记住的是，连接池和预处理（编译）语句都只是工具，它们有其限制，没有一种方法能够适用于所有可能的情况。在此基础上，让我们记住何时使用连接池和预处理语句。

使用连接池的可能原因

当连接很昂贵时，连接池非常有用，例如：

• 建立连接需要相当长的时间（连接到 SQL Server 或 Oracle DB 的网络连接），将打开的连接“缓存”起来以尝试提高系统性能是有益的。
• 连接在应用程序内（服务于多个并发请求的 Web 应用程序）或应用程序之间被限制并共享，因此必须尽快释放它们，以让其他客户端继续使用。

使用预处理语句的可能原因

预处理语句只是为了通过减少解析时间来提高可重用查询的性能。

SQLite：最佳选择是什么？

答案取决于您的应用程序要求。就我个人而言，我不确定 SQLite 连接池是否一定是一个好选择。如果您的应用程序是单线程的，最好使用一个永久连接到 SQLite DB，这可能比连接池更快，并且允许您使用预处理语句。这与 SQL Server 不同，连接池是一个非常合理的默认选择。

如果性能很重要，则应对应用程序进行分析，以确定 SQLite 连接池是否有益于您的情况。

具体问题

大多数答案都与当前的System.Data.SQLite提供程序源代码相关。

如果我打开和关闭连接（这可能意味着由于线程池而关闭连接），那么我从预处理语句中真正获得了多少好处？

通常情况下，你应该将从池中出来的连接视为新连接，也就是说，你不应该期望从之前准备好的语句中获得任何好处。除非你同时保留命令和连接，否则该语句将被“重新准备”。

但是，我认为在事务中保存单个对象并不会带来任何好处，因为一旦我关闭连接，从第一个对象生成的准备好的语句现在就消失了。这是一个正确的陈述吗？

这是一个正确的陈述。

如果我创建一个代表我经常执行的查询的SQLiteCommand，那么我需要在内存中保留该SQLiteCommand以保持准备好的语句处于活动状态吗？

是的，你需要保留它。 SQLiteCommand 持有对准备好的语句的引用。

如果我使用相同的SQLite语句创建一个新的SQLiteCommand，它是否会识别出新的SQLiteCommand与之前的相同，并且因此具有可以使用的准备好的语句？

我认为不支持。

如果我在打开和关闭不同事务的连接时更改SQLiteCommand的参数和连接，我是否在不同连接之间保持了一个准备好的语句？

如果你更改了 SQLiteCommand 的连接，该语句将被“重新准备”。

- Serge Belov

---

使用预处理语句的一个非常重要的附加理由，特别是对于插入和更新操作，是防止 SQL 注入攻击。预处理语句可以正确地转义要存储的值，并在所有情况下将其存储到数据库中。 - Berin Loritsch

Answer 2

我不确定核心问题是什么，但如果问题是如何在很短的时间内将批量插入语句插入到一个事务中。

这里有一个我之前发现的帮助类可以帮助你：

SQLiteBulkInsertHelper.cs

您可以像这样使用它：

SQLiteBulkInsertHelper ContactBlk = new SQLiteBulkInsertHelper("<SQLiteConnection>","<Table Name>");
ContactBlk.AllowBulkInsert = true;
ContactBlk.AddParameter("<Column Name>", /*Column Data Type*/System.Data.DbType.Int64);
ContactBlk.AddParameter("<Column Name>", /*Column Data Type*/System.Data.DbType.String);
ContactBlk.Insert(new object[] {<First Column Value>,<Second Column Value>});
ContactBlk.Flush();

如果您认为这是解决您问题的方案，请尝试一下。

- Mohamed Arabi

---

您发布的链接似乎已经失效了。（Chrome浏览器显示它是无限重定向循环） - Shadow The Spring Wizard

当然，您可以在循环中使用插入行，并在之后调用flush来提交事务。助手链接：SQLiteBulkInsertHelper.cs - Mohamed Arabi

好的，谢谢你在评论中发布的链接看起来不错。我已经在你的帖子中修复了它。 - Shadow The Spring Wizard