ADO Connections - close them or keep them open?

I surely don't want to start a religious debate here...
I'm curious - what is the conventional wisdom about using a single global co
nnection in a VB frontend (using ADO) and leaving that connection open for t
he entire time the app is running?
I've seen people suggest that you close the connection and re-open for every
query.
The specifics of my app is 1000+ users - dozens stay in all day long - other
s stay in for a short time (less than 30 minutes). Large WAN - dozens of bu
ildings across town.
Thanks in advance.My $.02 It depends on your licensing model and what you mean by stay in all
day. Do they actually run the application or just have it open. If it is
open and idle I believe Windows 2000 SP2 cuts idle connections after 20
minutes. (SP2 fixed a bug hat allowed unlimited open connection time on idle
connections)
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> I surely don't want to start a religious debate here...
> I'm curious - what is the conventional wisdom about using a single global
connection in a VB frontend (using ADO) and leaving that connection open for
the entire time the app is running?
> I've seen people suggest that you close the connection and re-open for
every query.
> The specifics of my app is 1000+ users - dozens stay in all day long -
others stay in for a short time (less than 30 minutes). Large WAN - dozens
of buildings across town.
> Thanks in advance.|||close it, set it to nothing and let connection pooling do its thing.
Cheers
Greg Jackson
PDX, Oregon|||My approach is ALWAYS get in, tweak the data, get out. Don't hold a
connection open longer than you have to.
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> I surely don't want to start a religious debate here...
> I'm curious - what is the conventional wisdom about using a single global
connection in a VB frontend (using ADO) and leaving that connection open for
the entire time the app is running?
> I've seen people suggest that you close the connection and re-open for
every query.
> The specifics of my app is 1000+ users - dozens stay in all day long -
others stay in for a short time (less than 30 minutes). Large WAN - dozens
of buildings across town.
> Thanks in advance.|||Why not hold a connection open? Why cause re-authentication of the user to
the DB?
If someone opens a maintenance form and starts calling up records - when wou
ld you decide they were done enough to close the connection?
What is the actual downside of a PC holding a connection open to the DB for
lets say - oh - 4 hours?
"Aaron [SQL Server MVP]" wrote:

> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> connection in a VB frontend (using ADO) and leaving that connection open f
or
> the entire time the app is running?
> every query.
> others stay in for a short time (less than 30 minutes). Large WAN - dozen
s
> of buildings across town.
>
>|||What is wrong with holding a connection open?
What is the cost of re-authenticating the user to the DB?
What is the actual downside of hold the connection - let's say - for 4 or 5
hours?
If a user goes into a maintenance form and starts working on a record to cha
nge it - what would be the moment you all thought that the connection is wor
th closing?
"Aaron [SQL Server MVP]" wrote:

> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> connection in a VB frontend (using ADO) and leaving that connection open f
or
> the entire time the app is running?
> every query.
> others stay in for a short time (less than 30 minutes). Large WAN - dozen
s
> of buildings across town.
>
>|||What is wrong with holding a connection open?
What is the cost of re-authenticating the user to the DB?
What is the actual downside of hold the connection - let's say - for 4 or 5
hours?
If a user goes into a maintenance form and starts working on a record to cha
nge it - what would be the moment you all thought that the connection is wor
th closing?
"Aaron [SQL Server MVP]" wrote:

> My approach is ALWAYS get in, tweak the data, get out. Don't hold a
> connection open longer than you have to.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steve Z" <Steve Z@.discussions.microsoft.com> wrote in message
> news:0B26EC78-F295-48D7-9BB7-85535E2C13F4@.microsoft.com...
> connection in a VB frontend (using ADO) and leaving that connection open f
or
> the entire time the app is running?
> every query.
> others stay in for a short time (less than 30 minutes). Large WAN - dozen
s
> of buildings across town.
>
>|||how do you share that connection between users ?
you are going to have to manage your own "pool" technically.
Keeping a global connection around is kind of an old mentality than in
today's environment does not make sense.
In short, your application will not scale at all if you attempt to do this.
Connection pooling built into OLE\DB pools connections so that the
authentication is not performed with each use of a shared connection.
trust me. you are trying to do manually, what will be done better for you
Automatically.
This is a well published "best practice" or "pattern" for data access in
Enterprise applications.
hope this helps, if you want more details other then "Because I said So"
just let me know and I'll point you to white papers galore (Starting with
"Data Access Best Practices" docs)
Cheers,
Greg J
PDX, Oregon|||Jaxon,
Every white paper I read is about WEB Access and ASP pages.
This is not what we have here. We have a school district - on an internal n
etwork - many buildings - 1000+ teachers. Teachers get into attendance app
for less than 30 minutes and post attendance for classes. Admin folks and s
ecretaries get in for the w
hole day - call up kids, start maintenance - get phone calls - interruptions
.
What do you mean "share the connection". I meant "global connection" in VB
- global to all the forms that the user might call up in the app - I didn't
mean global to all users.
Each workstation runs the app and makes a connection - no sharing involved.
We do all our server side work with SPROCS - so ADO calls the SPROC from VB
- returns the recordset and then the connection stays open until the next A
DO/SPROC call.
We use win-nt authentication - so taking advantage of SQL pooling of connect
ions so a similiar one can be found doesn't really make sense to us.
I've found info that says that establishing the connection can be time consu
ming - so why relinquish it and re-establish it'
Other programmers at this school district use ACCESS to touch there DB's.
I see that ACCESS opens "several" connections for just one user at a time -
this seems more of a waste of resources.
"Jaxon" wrote:

> how do you share that connection between users ?
> you are going to have to manage your own "pool" technically.
> Keeping a global connection around is kind of an old mentality than in
> today's environment does not make sense.
> In short, your application will not scale at all if you attempt to do this
.
> Connection pooling built into OLE\DB pools connections so that the
> authentication is not performed with each use of a shared connection.
>
> trust me. you are trying to do manually, what will be done better for you
> Automatically.
>
> This is a well published "best practice" or "pattern" for data access in
> Enterprise applications.
> hope this helps, if you want more details other then "Because I said So"
> just let me know and I'll point you to white papers galore (Starting with
> "Data Access Best Practices" docs)
>
> Cheers,
> Greg J
> PDX, Oregon
>
>|||see inline:

> Every white paper I read is about WEB Access and ASP pages.
There are many white papers on Data Access Strategies and best practices for
N-Tier Applications. It is essentially the same for Web Apps. Web apps can
be (and likely should be) designed as n-tier apps.

> This is not what we have here. We have a school district - on an internal
network - many buildings - 1000+ teachers. Teachers get into attendance app
for less than 30 minutes and post attendance for classes. Admin folks and
secretaries get in for the whole day - call up kids, start maintenance - get
phone calls - interruptions.
That's fine. This still works for N-Tier Architecture

> What do you mean "share the connection". I meant "global connection" in
VB - global to all the forms that the user might call up in the app - I
didn't mean global to all users.
if Each user keeps a Global Connection object in their App on the client
side, you likely will run out of connections quickly and requests will start
to queue up. This is not scalable as larger number of clients adds up.
Connections are a precious resource that should be obtained late and
released early so that connections dont become your system bottleneck.

> Each workstation runs the app and makes a connection - no sharing
involved. We do all our server side work with SPROCS - so ADO calls the
SPROC from VB - returns the recordset and then the connection stays open
until the next ADO/SPROC call.
This sounds like a class 2-tier or Client Server app to me then. likely too
late to turn it into an N-Tier App...you may be stuck. However, I would
still not keep global connections around.

> We use win-nt authentication - so taking advantage of SQL pooling of
connections so a similiar one can be found doesn't really make sense to us.
if each user logs in directly to sql server, you are rigth. You might want
to rethink this and have your code access the DB using a handful of standard
logins (ReadOnly, Read Write, REad Write Delete, Admin, Etc). Then you can
take advantage of pooling and you can scale better

> I've found info that says that establishing the connection can be time
consuming - so why relinquish it and re-establish it'
You're right. You dont want to do this. You want to try to pool connections.

> Other programmers at this school district use ACCESS to touch there DB's.
I see that ACCESS opens "several" connections for just one user at a time -
this seems more of a waste of resources.
Yes Access connectivity to a SQL Server DB can be downright crippling.
hope all this info helps. Sounds like you are already married to an
architecture that you wont be able to change. You may be stuck into doing
what you are currently doing...
Cheers
GAJ