Edit Question - KnowledgeHub

Title Body

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 n

Tags (comma-separated) Cancel