November 19, 2009 at 2:06 pm
Hi everyone! I've found some great help here in the past, so I'm hoping for similar results. 🙂
So I have this VB6 program that runs on a timer cycle. During each execution cycle, anywhere between 5 and 50 ADO calls will be made to a SQL 2000 database. I'm currently using the ADO 2.1 libraries.
When I originally coded this program, I figured it would be more efficient to instantiate the connection object when the program starts and then pass that connection object to each SP call, opening and closing the connection via the object as necessary to perform queries. My concern, however, is that this may actually be less efficient over an extended period of time than just instantiating a connection object for each query, as imperial evidence has shown that the program tends to have to wait longer for an ADO call to return during times of peak transaction levels if the program has been running for a while (ie. at least an hour).
So my question is: which is better? one connection object used over and over again, or create a new connection object for each query?
If this is not the right forum for this kind of question, please point me in the direction of a more appropriate one. Thank you for your time.
Sincerely,
Kevin
November 19, 2009 at 5:08 pm
Go to this support page:
http://support.microsoft.com/kb/827422
Note If you are connecting to a named instance of SQL Server, make sure that you are running MDAC 2.6 or later on your computer. Earlier versions of MDAC do not recognize named instances of SQL Server. Therefore, connections to named instances may not be successful.
When you use the SQL Server ODBC driver, the Microsoft OLE DB Provider for SQL Server, or the System.Data.SqlClient managed provider, you can disable connection pooling by using the appropriate application programming interfaces (APIs). When you disable connection pooling and your application frequently opens and closes connections, the stress on the underlying SQL Server network library may increase.
Do not know if there are other differences between MDAC 2.1 and MDAC 2.6 but reading the support document may be helpful. I remember something about connection pooling when coding using VB6 and ADO.. I had to be very careful about closing a connection. I choose to keep the single connection open and then using the connection object to alter the stored procedure name and then re-execute the command ...
Anyway hope that the link assists you in making your decision.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply