[AccessD] Connections and Performance

Dan Waters dwaters at usinternet.com
Tue Feb 6 14:15:23 CST 2007


Jim,

In a system set up where each FE has a connection automatically established,
about how many FE's could do this?

I use Edited Record for my databases - this is equivalent to pessimistic
locking for Jet, but does Windows Server 2003 supersede the Access setting?

In a book I have called MS Jet Database Engine Programmer's Guide (pub.
1997), it talks about a ConnectionTimeout registry key for Jet that is by
default set to time out an inactive connection after 10 minutes (600 seconds
on my PC).  The book suggested that you can change the value using the
SetOption method in code, but I believe that the SetOption method actually
can't be used for this particular key.  It seems as though in an environment
where FE apps are competing for connections that reducing this value could
be beneficial.  

Dan Waters


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Tuesday, February 06, 2007 12:42 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

Drew,

  That practice still should be followed.  If you allow a BE to close, there
is a lot of overhead that Access/JET goes through opening it back up.  Can
make a big difference.  And it can be any reference to a DB.  Even just
opening the database in code would keep the connection established.  

Dan,

  If the BE is on a NT/2000/2003 server, you may want to investigate turning
off opportunistic locking.  Be careful with this though because if other
apps are running off that server, they may be negatively impacted.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Monday, February 05, 2007 7:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

I believe there is only one connection between a front end and back end.
In fact, many databases have better performance when the connection is
maintained through a 'linked table'.  Create a dummy table (one field,
no records), called tblLink.  Create a form bound to that table, and
open that form, hidden, on startup.  That keeps the 'connection' live
while the user is in the database.

I know in Access 97 that process was recommended in the help files under
'splitting a database'.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Monday, February 05, 2007 6:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

Thanks Drew,

I have asked everyone at my customer's site to close the screens they're
not
using (these are bound), but told them they can stay logged in because
the
main screen is not bound.  We'll see if that helps.  If not then I will
need
to start timing out the bound forms.

I still don't know what is the definition of one connection.  Is one
bound
form a connection?  Is a FE with any number of bound forms a connection?
Or
is a connection defined by the periods of time when data transfer is
taking
place between a FE and BE?  Or . . . ?


Thanks for teaching me!
Dan Waters


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Monday, February 05, 2007 4:06 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

Most 'articles' that 'document' Access performance are doing so based
upon poorly designed systems.  You have to design your system based on
system requirements.  If you are going to have 1 to 5 users, you can
keep everything bound, and be reasonably assured that things will work
well.  If you are talking 5 to 30 users, now it's time to pick up some
of the slack.  Timeout forms and reports that are going to tie up db
connections.  30 to 100 users, you better start thinking unbound.  100+
users, better start thinking about something other then Access as a
front end (I would recommend ASP).

The point is that JET, as a db system, can handle a lot, but Access, as
a Front End provides more bells and whistles that tax the backend more.
Case in point, we have our production database in Oracle, on it's own
honking box.  Huge server, loads of memory and disk space, but when
certain things are run, even that system gets taken to It's knees.  

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 02, 2007 10:13 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

Drew,

I read this somewhere - either in MS documentation or in a book where I
believe the author. 

Here's the reason I was asking:

At a certain customer site, they experienced a dramatic performance
slowdown
at a time when about 10 people were logged on concurrently.  Each client
PC
has it's own FE.  I know that they open the system and leave the process
screens open, all of which are bound.  I want to suggest that they
remember
to close the process screens so that just the main screen is open, which
is
not bound.  This way a fewer number of connections are being used at any
one
time and performance would probably be acceptable.  This particular
customer, I believe, does not have a very good network, so that is part
of
the problem.  But that's unlikely to get improved, so I wanted to
provide at
least a partial solution, hence my question.

I've also heard that a connection is made not based on whether a form is
open and bound, but on whether or not there is data traffic between to
and
from a table, which only takes a small part of a second.

So what causes the performance slowdown when only 10 client PC's are
logged
on, and what could be done to improve this?

Dan Waters

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, February 02, 2007 9:26 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

Where are you getting these numbers.  Access can have up to 255
connections.

Drew

-----Original Message-----
From: Dan Waters [mailto:dwaters at usinternet.com] 
Sent: Friday, February 02, 2007 8:45 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

Access has the potential for 8 simultaneous connections, but 3 of them
are
reserved for the system's use.  That leaves five for users.

Dan Waters


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, February 02, 2007 8:02 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

>But, the connection limit for one BE is 5 FE's.

What does this mean?  I have never heard of any such thing.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 02, 2007 8:58 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Connections and Performance

I've read several times that maintaining a connection between a FE and a
BE
will increase the performance of the FE because it doesn't need to
reconnect
before transferring data.  The connection here would be a bound form
connected by a table link to a table in the BE.

But, the connection limit for one BE is 5 FE's.  So, will maintaining
connections on more than 5 FE's reduce performance?  Seems logical, but
I
was wondering if this is correct or is there more to it?

Thanks!

Dan Waters

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list