Global connection object vs connection string
Patrick Jackman
SQLServer 2008 backend would your recommendation be to hold open a global
connection object for the life of the app or create a new connection each
time one is required? It's a server license.
A: By a global connection object I mean an approach like this:
Public Enum alSQLConnect
alSQLODBC
alSQLOLEDB
End Enum
Private mcnn as ADODB.Connection
Public Function SQLServerConnect(ByRef ral As alSQLConnect) As String
If ral = alSQLODBC Then
SQLServerConnect = "ODBC;Driver={SQL Server Native Client
10.0};Server=Homer;Database=BLIS;Trusted_Connection=Yes"
Else
SQLServerConnect = "Provider=SQLNCLI10;Data Source=Homer;Initial
Catalog=BLIS;Integrated Security=SSPI"
End If
End Function
Public Function SQLServerCnn() As ADODB.Connection
If mcnn Is Nothing Then
Set mcnn = New ADODB.Connection
mcnn.Open SQLServerConnect(alSQLOLEDB)
ElseIf mcnn.State = adStateClosed Then
mcnn.Open SQLServerConnect(alSQLOLEDB)
End If
Set SQLServerCnn = mcnn
End Function
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = SQLServerCnn
.CommandText = "uvg.usp_task_update_agent"
.CommandType = adCmdStoredProc
.Execute , Array(vlngTaskID, vstrTaskAgent, CurrentUser()),
adExecuteNoRecords
End With
Set cmd = Nothing
B: By create a new connection each time I mean an approach like this:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = SQLServerConnect(alSQLOLEDB)
.CommandText = "usr.usp_task_update_agent"
.CommandType = adCmdStoredProc
.Execute , Array(vlngTaskID, vstrTaskAgent, CurrentUser()),
adExecuteNoRecords
End With
Set cmd = Nothing
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC
Stefan Hoffmann
On 04.05.2010 21:42, Patrick Jackman wrote:
> In an Access 2002 application using stored procedures to write all data to a
> SQLServer 2008 backend would your recommendation be to hold open a global
> connection object for the life of the app or create a new connection each
> time one is required? It's a server license.
For client performance you may use a "global" connection object as it
saves yuo time of creating it. Problems may arise on the server side as
open connections require server resources. So in an environment with
many users this may lead to slower response times of SQL Server.
On the other hand using always a newly created object is also a problem
as you may later use your functions on a higher level, e.g. a for loop
using the function or a report. This may reduce in creating a lot of
objects thus dropping the client performance. So basically you have to
decide what you do.
But you should also be aware of connection pooling (Code Example 3):
http://msdn.microsoft.com/en-us/library/ms810829.aspx
The probably best solution depends on your actual needs. I would use an
approach somewhere located in the middle (in a standard module, untested):
Option Compare Database
Option Explicit
Private m_Connection as ADODB.Connection
Private m_ConnectionString as String
Public Property Get Connection() As ADODB.Connection
If m_Connection Is Nothing Then
Set m_Connection = New ADODB.Connection(m_ConnectionString)
End If
Set Connection = m_ConnectionString
End Property
Public Property Get ConnectionString() As String
ConnectionString = m_ConnectionString
End Property
Public Property Let ConnectionString(AValue As String) As String
m_ConnectionString = AValue
End Property
Public Sub FreeConnection()
If Not m_Connection Is Nothing Then
m_Connection.Close
Set m_Connection = Nothing
End If
End Sub
Public Function IsConnectionValid()
' Place the necessary error handling in this method.
' return true if connection is valid.
' Test the connection occasionally, but at least before the first use.
On Local Error GoTo LocalError
Dim cm As ADODB.Command
Set cm = New ADODB.Command
Set cm.Connection = Connection
'Make a simple Count(*) on an always readable table or
'use a SELECT @@version;
IsConnectionValid = True
Exit Function
LocalError:
IsConnectionValid = False
End Sub
mfG
--> stefan <--
Patrick Jackman
link.
I have never seen this before; is it legal or a typo?
On Local Error GoTo ...
Patrick.
"Stefan Hoffmann" <ste...@ste5an.de> wrote in message
news:uOM9jeC...@TK2MSFTNGP02.phx.gbl...
Stefan Hoffmann
On 06.05.2010 06:47, Patrick Jackman wrote:
> I have never seen this before; is it legal or a typo?
> On Local Error GoTo ...
Nope, it's old school deprecated syntax... I'd simply prefer it.
mfG
--> stefan <--
Sylvain Lafontaine
global connection object for performance reason; it's not necessary to do
the same when working against a SQL-Server database. SQL-Server does not
work the same way and as you're probably not the only one person working
against it, you should create and close a connection each time one is
required in order to save resources on the SQL-Server and take advantage of
its connection pooling feature.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
"Patrick Jackman" <pjackman at wimsey no spam com> wrote in message
news:eQ3npH86...@TK2MSFTNGP06.phx.gbl...
Patrick Jackman
implemented at the moment so I'm happy to read this.
Patrick.
"Sylvain Lafontaine" <sylvainlaf...@yahoo.ca> wrote in message
news:uKHThXV7...@TK2MSFTNGP02.phx.gbl...
Armen Stein
<sylvainlaf...@yahoo.ca> wrote:
>While it's often important for a Jet based backend database to keep open a
>global connection object for performance reason; it's not necessary to do
>the same when working against a SQL-Server database.
Hi Sylvain,
That's true for performance reasons, but we use the practice of
opening a global DAO recordset object to a small back-end table
(snapshot mode and just one record) and leaving it open for the
duration of the application. It's just for persistent authentication
- without this approach, we were seeing spurious prompting for
database credentials when tables were accessed later, even if we
specified to remember the password. Maybe there's a different way
around this problem - if so I'd be interested in hearing about it.
Cheers,
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Sylvain Lafontaine
deleting and recreate all the links with the save password specification.
If this doesn't work, try compacting and repairing the database: create a
shortcut with the following options:
/compact /repair /decompile
It's better to perform this step on each target machine. If none of this
work, try using another protocol. For example, if you are using named
pipes, try with TCP/IP instead and vice-versa. If this is not the default
instance of SQL-Server (ie., it's a named instance), try to set up the named
instance to a specific port and to specifiy it after the address of the
server (instead of relying on the SQL-Server Browsing service along with a
dynamically allocated port).
Creating a alias for the server and us it instead could also be a good thing
as this will give you an easy control over the protocol used.
What kind of authentification (Windows or SQL-Server) are you using?
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
"Armen Stein" <Armen...@removethisgmail.com> wrote in message
news:sbi8u5t49b7bn4bo7...@4ax.com...
Armen Stein
We've seen this issue for a long time, and I'm not sure when we'll
take the time to re-investigate, as the global recordset solution does
work universally. However, I'll address your points as I can remember
them.
>The spurious prompting for database credentials is a strange problem. Try
>deleting and recreate all the links with the save password specification.
>If this doesn't work, try compacting and repairing the database: create a
>shortcut with the following options:
>
>/compact /repair /decompile
We C&R and de compile all the time during our development, so that
can't be it.
>It's better to perform this step on each target machine.
We build applications and distribute them to many users in many
companies, so we need something that we can do in one place.
>If none of this
>work, try using another protocol. For example, if you are using named
>pipes, try with TCP/IP instead and vice-versa. If this is not the default
>instance of SQL-Server (ie., it's a named instance), try to set up the named
>instance to a specific port and to specifiy it after the address of the
>server (instead of relying on the SQL-Server Browsing service along with a
>dynamically allocated port).
We always use TCP/IP, as our applications sometimes need to connect
over the internet. We often specify a special port (not 1433) in
order to be routed to the correct server.
>Creating a alias for the server and us it instead could also be a good thing
>as this will give you an easy control over the protocol used.
I'm not sure what kind of alias you mean. On the client? Server?
DNS?
>What kind of authentification (Windows or SQL-Server) are you using?
I don't think this problem would occur with Windows Auth. We almost
always use SQL Auth because we sometimes connect to a remote server as
mentioned above.
Thanks,
Armen
Sylvain Lafontaine
On the client: from the SQL-Server Configuration Manager, you can create an
Alias for a server where you can specify not only its new name (alias) but
also the protocol to use. As for the name of the alias, you can specify
about anything; including a TCP/IP address or a Windows address name.
These aliases will have priority over the real TCP/IP address or Windows
name when used in a connection string for SQL-Server.
However, as you have this problem at many locations, I would say it's more
likely to be a bug of Access than anything else; unless it's the connections
pooling feature of SQL-Server that is again the culprit. Especially with
SQL-Server 7, I remember some problems related to this connections pooling
feature.
The problem with these connection problems is that they are very difficult
to debug as Access or SQL-Server don't give us much information when there
is a problem.
>>What kind of authentification (Windows or SQL-Server) are you using?
>
> I don't think this problem would occur with Windows Auth. We almost
> always use SQL Auth because we sometimes connect to a remote server as
> mentioned above.
>
> Thanks,
> Armen
>
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
--
Patrick Jackman
My Access application uses linked views, Access queries and DAO for a all
data reading, and stored procedures, pass through queries (where Table
Valued Parameters are involved) and ADO for all data writing.
In the article "Pooling in the Microsoft Data Access Components" at
http://msdn.microsoft.com/en-us/library/ms810829.aspx I read the following:
*****
Writing Data Consumers That Work With OLE DB Services
When developing an application using either ADO or native OLE DB, the
following tips will help you to enable resource pooling or to avoid
inadvertently disabling it. These rules apply whether you use ADO, OLE DB
consumer templates, or native OLE DB code.
Tips for ADO Users
* The ADO Connection object implicitly uses IDataInitialize. However,
this means your application needs to keep at least one instance of a
Connection object instantiated for each unique user-at all times. Otherwise,
the pool will be destroyed when the last Connection object for that string
is closed. (The exception to this is if you use Microsoft Transaction
Server. In this case, the pool is destroyed only if all of the connections
in the pool have been closed by client applications and are allowed to time
out.)
Note If you open a Connection object, remember to close it. Do
not rely on garbage collection to implicitly close your connections.
* By avoiding connection creep (discussed in section "Connection Creep
and Effective Server Tracing" later in this article), you can also help your
application use pooling.
*****
This seems to be at odds with what you are suggesting below. I interpret
this article to recommend something like what Armen is doing: open a
connection to a small table and hold it open for the duration of the App's
session and then open and close all other connection objects as they are
used. Or am I misunderstanding something?
Patrick.
"Sylvain Lafontaine" <sylvainlaf...@yahoo.ca> wrote in message
news:uKHThXV7...@TK2MSFTNGP02.phx.gbl...
Sylvain Lafontaine
Well, it's a certainty that if you don't keep any connection open to the
SQL-Server, the pool will be deleted 60 seconds after the last connection
close. However, this article is based on documentation from the late of the
nineties (1995+) and on a modern machine, recreating a pool should be seen
as a nearly instantaneous process.
Furthermore, even when you already have an hard connection open to
SQL-Server, opening a second hard connection won't be any faster. So, for
the sake of saving a few micro-seconds for recreating a new pool after 60
seconds of total inactivity, you are keeping a supplemental - and quite
probably unused - hard connection to SQL-Server for each user.
The same thing is not true for JET because in the past, many people have
shown that opening a new connection is faster when you already have an open
connection. This is not only a question of maintening a pool of open
connections but also a question a question of opening a new hard connection
when there is either none or at least one hard connection already open and
this problem is worsened by the fact that even on a very fast client
machine, such a process is limited not only by the speed of the server but
also by the speed of the network and of the other JET clients accessing this
server.
With SQL-Server, you are much more immune about the interaction with the
existence or the absence of an already opened hard connection, the speed of
the network and the presence of other connected clients when creating a new
hard connection to the server than with JET/Access.
The only thing common about both would be the time required to create a new
pool and the local connection objets inside it but on a modern client
machine, this is a zappy process; anything worth to worry about especially
after 60 seconds of inactivity.
Armen Stein
wimsey no spam com> wrote:
> I interpret
>this article to recommend something like what Armen is doing: open a
>connection to a small table and hold it open for the duration of the App's
>session and then open and close all other connection objects as they are
>used.
Yes, but as I said before, we hold the one global connection open to
avoid security prompting, not necessarily for performance. If it also
improves performance (which I'm not sure it does, at least
measurably), then that's a side benefit.