[AccessD] Connections and Performance

Dan Waters dwaters at usinternet.com
Tue Feb 6 18:01:05 CST 2007


Thanks Drew - that might work for some processes.

Dan Waters

-----Original Message-----
Subject: Re: [AccessD] Connections and Performance

Ok, process records, let me see if I can take a stab at that.

Let's say you have an insurance case.  It gets assigned a case number,
and you are allowing people to edit the details, as new things happen.
This kind of concept should be redesigned so that each individual entry
is a new record.  It's a simple design, the case number and one time
details would be in one table, then a separate table would hold
additional entries, each with the foreign key from the original case
number.  This way, you can view all 'work' done on the case, but each
'change' is a new record, which is just added to the db, instead of
tying up the entire record during editing.

Drew

-----Original Message-----
Subject: Re: [AccessD] Connections and Performance

Yes - lot's of editing.  These are mostly process records where a single
record can stay available for editing for days or weeks before the
record is
finally closed.

I do have a Vacation Management module which is mostly adding records
though.  But, due to screen graphics redisplay, it's the slowest of the
bunch!  I'm going to try to work through this one to see if I can speed
it
up.

Dan Waters


-----Original Message-----
Subject: Re: [AccessD] Connections and Performance

Do you have a lot of people editing data?  That is a design issue that
will cut the performance.  Instead of editing, you should add.  I know
it won't work for everything, but it is feasible in many situations.

Drew

-----Original Message-----
Subject: Re: [AccessD] Connections and Performance

Thanks Mark!

That sounds quite similar to what I do now.  Lots of input/output and
FE/BE.

I'll try the continuous connection technique to see how that works.
I'll
have to wait till the next time I'm on site though.

Dan Waters

-----Original Message-----
Subject: Re: [AccessD] Connections and Performance

Dan,

My experience with this is around is about 6 years ago and with A97.
Most 
of my decisions then were based on this group's input.  At that time the

average feedback I received with Access as the FE and BE...was 40
users... 
after that corruption...so I limited them to 40 users.  There were a
number 
of things I did to increase performance.  I had a seperate computer that

housed the BE.  Nothing else was done with this machine(just an extra 
desktop we had).  FEs on each users machine.  This app was a complaint 
management system...so continuous lookup/edit existing/adding new.  I
had an

unbound searchscreen...it had a couple of bound subforms to show each
user 
the stats of their cases...and the search results subform was unbound
until 
you hit search.  The form to display/edit the case record did not use
the 
filter property...the 'record source' SQL used the searchform as
criteria.

Hope this helps...let me know if you have any specific questions.

Thanks,

Mark A. Matte


>From: "Dan Waters" <dwaters at usinternet.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Connections and Performance
>Date: Tue, 6 Feb 2007 08:57:15 -0600
>
>Hi Drew,
>
>Do you know how many FE clients could maintain that type of connection
to a
>BE at the same and still have reasonable performance?
>
>Does anyone have experience with this configuration?
>
>Dan Waters
>
>-----Original Message-----
>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-----
>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

_________________________________________________________________
Laugh, share and connect with Windows Live Messenger 
http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http:/
/ima
gine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=hmtagli
ne

-- 
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