SQLite的/ C＃连接池和预处理语句混乱 [英] SQLite/C# Connection Pooling and Prepared Statement Confusion
问题描述
我已经花了一些时间阅读数据库和SQLite的具体不同的最佳实践。在阅读我发现我在做很多事情我不应该做的，并试图解决这些问题，当我成为思考的一些使用SQLite与它的ADO实施更精细的细节时感到困惑。
我的困惑预处理语句和连接池专门茎。
在阅读 http://msdn.microsoft .COM / EN-US /库/ ms971481.aspx 我发现，连接应仅适用于交易被打开。一旦交易完成那么连接应该关闭。我没有牢固掌握，为什么是这样的话，但我一直在努力了，前提是作者（S）知道更好，然后我。我明白，当一个连接被关闭，这并不意味着它实际上已经被关闭。它只是意味着它已被放回池中。
现在，来提高我的查询和插入我读到使用预处理语句。 SQLite中，你准备语句真正提高性能？和< A HREF =http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html> http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements html的都似乎表明，执行将要进行查询时多次准备语句的路要走。我也看过，一个事先准备好的声明是针对一个连接，一旦连接关闭预处理语句都将丢失。
我的困惑是这样的。如果我打开和关闭我的连接（这可能会或可能不会意味着由于连接被关闭线程池），然后使用多少我真的从一份声明中得到些什么？我可以理解，如果我有1000个对象，我需要在一个事务中，该准备语句可以有很大的帮助，以节省。不过，我不相信我会看到在一个事务中保存单个对象的好处，因为一旦我关闭连接，这是从第一对象生成现在失去了准备好的声明。这是一个真实的陈述？
我的困惑是事实，我相信一个准备好的语句链接到我的SQLiteCommand对象的范围进一步发展。
如果我创建一个代表，我将执行一个查询经常做我需要保持的SQLiteCommand在内存中准备好的声明中保持活跃SQLiteCommand？
如果我创建一个具有相同SQLite的说法是它认识到，新SQLiteCommand相同以前一个新的SQLiteCommand，因而具有可以使用事先准备好的声明？
如果我把内存中的一个SQLiteCommand，改变它的参数和连接，我打开和关闭不同的交易连接我在本质上保持一份声明中不同的连接之间还活着吗？
我在这一点上最有可能在思考的事情，但我希望你能帮助我更好地了解这些东西互动，所以我可以得到最大的利益了出来。
它有助于记住，这两个连接池和准备（编译）声明只是有其局限性，没有方法可以同样适用于所有可能发生的情况的工具。考虑到这一点，让我们记住，当一个可能需要使用连接池和准备的语句。
可能原因使用连接池
连接池是有用的：
- 它需要的显著时间的建立连接（网络连接到SQL Server或Oracle DB），这是为了改善系统性能，以缓存打开的连接是有益的。
- 连接是的有限和共享的应用程序内（从Web应用程序服务于多个并发请求连接）或应用程序之间，使它们必须尽快公布，让其他客户端继续。
的可能原因使用预准备语句
准备语句。只是为了提高通过减少解析时间可重复使用的查询性能。
SQLite的？什么是最好的选择
问题的答案取决于你的应用需求。就个人而言，我不知道是否SQLite的连接池必然是一个不错的选择。如果你的应用程序是单线程的，这可能是最好的使用SQLite的数据库，这可能是比池快得多，将允许您使用预准备语句过于单一的永久连接。这是从SQL Server，其中连接池是一个非常合理的默认值不同。
如果性能问题，你一定要分析的应用程序，看看是否SQLite的连接池是有益的您的方案。
的具体问题
大部分的答案都涉及到电流 System.Data.SQLite
如果我打开和关闭我的连接（这可能b将连接正在关闭，或者可能并不意味着$ b $由于线程池），然后多少
使用我是真正从一份声明中得到些什么？
一般情况下，你应该治疗连接出山池新的，也就是你不应该指望从预先准备的声明得到任何好处。该语句将被重新编制除非你保留两个命令连接。
不过，我不相信我会看到一个从节约了交易单
对象，因为一旦我关闭连接
事先准备好的声明说从第一对象生成有利于现在
丢失。这是一个真实的陈述？
这是一个真实的陈述。
如果我创建一个代表一个查询，我将是
执行经常做我需要保持的SQLiteCommand在内存中为
准备好的声明中保持活跃SQLiteCommand？
是的，你需要保持它。
SQLiteCommand保存到准备好的语句引用。
如果我创建一个新SQLiteCommand用同样的SQLite语句是
认识到，新SQLiteCommand相同从而先前和
的可用于准备语句
我不认为它的支持。
如果我保持SQLiteCommand在内存中，并改变它的参数
连接，我打开和关闭不同的
交易的连接我是基本上保持不同的连接之间的一份声明中活着
？
如果你改变了
SQLiteCommand的连接，该语句将被重新编写。I have been spending some time reading different best practices for databases and for SQLite specifically. While reading I found I was doing many things I shouldn't be doing and when attempting to fix these issues I became confused when thinking about some of the finer details of using SQLite with it's ADO implementation.
My confusion stems specifically from prepared statements and connection pooling.
While reading http://msdn.microsoft.com/en-us/library/ms971481.aspx I found that connections should only be opened for a transaction. Once the transaction is complete then the connection should be closed. I do not have a firm grasp as to why this is the case, but I have been working off the assumption that the author(s) know better then I. I understand that when a connection is closed it doesn't mean it has actually been closed. It simply means that it has been put back into the pool.
Now to improve my queries and inserts I read about using prepared statements. In SQLite, do prepared statements really improve performance? and http://petesbloggerama.blogspot.com/2007/02/sqlite-adonet-prepared-statements.html both seemed to indicate that when executing a query that will be done multiple times prepared statements are the way to go. I have also read that a prepared statement is specific to a connection and that once the connection is closed the prepared statement is lost.
My confusion is this. If I am opening and closing my connection (which may or may not mean the connection is being closed due to the thread pool) then how much use am I really getting from a prepared statement? I can understand that if I have 1000 objects I need to save in a single transaction that the prepared statement can help a lot. However I don't believe I would see a benefit from saving a single object in a transaction because once I close the connection the prepared statement that was generated from the first object is now lost. Is this a true statement?
My confusion is furthered by the fact that I believe a prepared statement is linked to the scope of my SQLiteCommand object.
If I create a SQLiteCommand that represents a query that I will be executing often do I need to keep that SQLiteCommand in memory for the prepared statement to stay active?
If I create a new SQLiteCommand with the same SQLite statement is it recognized that the new SQLiteCommand is the same as the previous and thus has a prepared statement that can be used?
If I keep a SQLiteCommand in memory and change it's parameters and connection as I open and close the connection for different transactions am I essentially keeping a prepared statement alive between different connections?
I am most likely over thinking things at this point but I hope you can help me better understand how these things interact so I can get the most benefit out of them.
解决方案It helps to remember that both connection pooling and prepared (compiled) statements are just tools that have their limits and no approach can be equally suitable to all possible situations. With this in mind, let's remember when one might want to use connection pooling and prepared statements.
Possible Reasons to Use Connection Pooling
Connection pooling is useful when connections are expensive, for example:
- It takes significant time to establish a connection (network connections to a SQL Server or Oracle DB) and it is beneficial to "cache" open connections in an attempt to improve system performance.
- Connections are limited and shared within an application (connections from a web application serving multiple concurrent requests) or between applications so they have to be released as soon as possible to let the other clients continue.
Possible Reasons to Use Prepared Statements
Prepared statements are simply meant to improve performance of re-usable queries by cutting down the parsing time.
SQLite: What's the Best Choice?
The answer depends on your application requirements. Personally, I'm not sure if SQLite connection pooling is necessarily a good choice. If your application is single-threaded, it might be best to use a single permanent connection to the SQLite DB, which could be much faster than pooling and would allow you to use prepared statements too. This is different from SQL Server where connection pooling is a very reasonable default.
If performance matters, you should definitely profile the application to see if the SQLite connection pooling is beneficial for your scenario.
Specific Questions
Most of the answers are related to the current
System.Data.SQLiteprovider source.If I am opening and closing my connection (which may or may not mean the connection is being closed due to the thread pool) then how much use am I really getting from a prepared statement?
Generally, you should treat a connection coming out of the pool as new, i.e. you should not expect to get any benefit from statements prepared previously. The statement will be "re-prepared" unless you keep both the command and connection.
However I don't believe I would see a benefit from saving a single object in a transaction because once I close the connection the prepared statement that was generated from the first object is now lost. Is this a true statement?
This is a true statement.
If I create a SQLiteCommand that represents a query that I will be executing often do I need to keep that SQLiteCommand in memory for the prepared statement to stay active?
Yes, you need to keep it.
SQLiteCommandholds a reference to the prepared statement.If I create a new SQLiteCommand with the same SQLite statement is it recognized that the new SQLiteCommand is the same as the previous and thus has a prepared statement that can be used?
I don't think it's supported.
If I keep a SQLiteCommand in memory and change it's parameters and connection as I open and close the connection for different transactions am I essentially keeping a prepared statement alive between different connections?
If you change the
SQLiteCommand's connection, the statement will be "re-prepared".这篇关于SQLite的/ C＃连接池和预处理语句混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
