[dba-SQLServer] Holding a SQL Server connection open

jwcolby jwcolby at colbyconsulting.com
Sat May 5 07:49:54 CDT 2012


My Address Validation program caches records in sql server into class instances, then updates 
properties on those records (in those classes) and in many cases writes the changes back to SQL 
Server immediately.  What this means is that a specific field of the record is updated in the class 
and in SQL Server as some processing step is completed.

I am having a connectivity issue in my network where every once in a while the flag write will not 
manage to open the connection to SQL Server before timing out.

 From a system design perspective I need guidance on what I am doing.  ATM the flag actually opens a 
connection, writes the data to the field of the specific record then closes the connection.  While 
that certainly works, it is causing (or finding) problems with the occasional "can't connect", plus 
it seems logical that it is slower than just having a shared connection that is kept open.

I have been coached to keep connections open for the minimum time necessary, but I am wondering 
whether it would be appropriate in this case to get a connection, hold it open as long as the 
program is open, then share that connection.

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it



More information about the dba-SQLServer mailing list