473,836 Members | 1,556 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What techniques are key to Access performance with separate front-endand back-end Access implementation via a LAN?

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation? ??

Thanks

Bob Alston
Nov 13 '05 #1
24 2795
On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu************ ****@cox.net>
wrote:
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation? ??

Thanks

Bob Alston


Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.
Nov 13 '05 #2
Steve Jorgensen wrote:
On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu************ ****@cox.net>
wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation? ??

Thanks

Bob Alston

Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.

Thanks Steve. Really approciate your experience.

Can you tell me what level of and frequency of reliability problems you
have experienced in "server back-end" Access only applications?

I have seen and tried to help trouble shoot some of these but I did not
develop the application and never got very far into exactly how the app
worked.

Bob
Nov 13 '05 #3
We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
(this is rare, and not the normal case, but it does point out that ms-access
does NOT always drag the whole table to the client)

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKal...rch/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...b etter and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..rest rict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

Last by not least, networks are a shared resource, and if you got two users
pulling data, you got 1/2 the perfoamcne. So, 10 users = 1/10 the speed.

I talk about the limited bandiwth issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #4
On Sun, 16 Oct 2005 13:35:14 -0500, Bob Alston <tu************ ****@cox.net>
wrote:
Steve Jorgensen wrote:
On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu************ ****@cox.net>
wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation? ??

Thanks

Bob Alston

Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.

Thanks Steve. Really approciate your experience.

Can you tell me what level of and frequency of reliability problems you
have experienced in "server back-end" Access only applications?

I have seen and tried to help trouble shoot some of these but I did not
develop the application and never got very far into exactly how the app
worked.

Bob


With a shared MDB, the worst reliability problems occur when the front-end
database is shared, so the first thing is - don't do that. Otherwise, here's
what happens as the number of users increases...

1. The odds that one or more users has a network connection problem that can
corrupt the database goes up. At a high enough number of users, that's almost
guaranteed to be an issue, and good luck trying to find out who it is.

2. It becomes harder and harder to take the database down for unscheduled
maintenance. Often, when MDB corruption happens, it's actually very minor,
and doesn't affect users who have the database open, just new users trying to
get in. Still, to fix the problem, you have to get everyone out. If that's
20 people, some of them are in meetings, and some of them are at lunch, that
can be no fun. If you're the IT admin, or if you have immediate access to the
IT admin, you can close the open file handles, but that risks losing some
users' unsaved work, and the users may then have to use task manager to get
out of Access, so they can restart it.

3. It becomes harder to take the database down for -scheduled- maintenance.
In order to prevent problems and keep performance snappy, you need to
periodically compact and repair the database. The more users, the more often
this should be done. At over 25 users, it should be done nightly. Now, with
25 users, try to find a time when no one is using the application, so you can
do the maintenace. You may think midnight would be a good time, and you might
be wrong.
Nov 13 '05 #5
On Sun, 16 Oct 2005 12:07:49 -0700, Steve Jorgensen <no****@nospam. nospam>
wrote:

....

With a shared MDB, the worst reliability problems occur when the front-end
database is shared, so the first thing is - don't do that. Otherwise, here's
what happens as the number of users increases...


I realized the above is unclear. What I meant by that is, "If you're already
doing that part right, here's what you still have to watch out for..."
Nov 13 '05 #6
Albert D. Kallal wrote:
We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
(this is rare, and not the normal case, but it does point out that ms-access
does NOT always drag the whole table to the client)

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKal...rch/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...b etter and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..rest rict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

Last by not least, networks are a shared resource, and if you got two users
pulling data, you got 1/2 the perfoamcne. So, 10 users = 1/10 the speed.

I talk about the limited bandiwth issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Thank you Steve and Albert! I really do appreciate it.

Bob
Nov 13 '05 #7
Bob Alston wrote:
Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done
higher volume access implementations use to ensure high performance
of the database in a multi-user 100mbps LAN implementation? ??

Thanks

Bob Alston


As with a true Client/Server setup one of the main things is to minimize how
much data is pulled over the wire.

Never open a form exposing the full Recordset.

Proper use of indexes on all required fields.

Inhibit free-form use of the built in Find tools.

Provide searching tools that use the minimum amount of data to let the user then
choose the one (or very small number) of full records to view.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #8
Steve Jorgensen <no****@nospam. nospam> wrote in
news:9k******** *************** *********@4ax.c om:
3. It becomes harder to take the database down for -scheduled-
maintenance. In order to prevent problems and keep performance
snappy, you need to periodically compact and repair the database.
The more users, the more often this should be done. At over 25
users, it should be done nightly. Now, with 25 users, try to find
a time when no one is using the application, so you can do the
maintenace. You may think midnight would be a good time, and you
might be wrong.


Well, one solution to this is to implement a timer in an always-open
form that at, say 11pm informs any user who is logged on that they
have until 11:45 to finish their work. If the user doesn't dismiss
this dialog, then the code will execute at 11:45, and what it does
is walk the Forms collection, save all data in all dirty open bound
forms (including subforms) and close all the forms and then exit the
database. I've implemented this kind of code in several apps over
the years, and it works pretty well.

I've also toyed with using a message table on the server to initiate
the same process at an ad hoc time, in order to tell people in an
office that we need to take down the database, and then using the
same approach for any unattended PC as outlined above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9
"Albert D. Kallal" <ka****@msn.com > wrote in
news:xjx4f.2145 47$tl2.80373@pd 7tw3no:
I even seem some very cool numbers showing that some queries where
actually MORE EFFICIENT in terms of network use by JET then sql
server. (this is rare, and not the normal case, but it does point
out that ms-access does NOT always drag the whole table to the
client)


Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed field,
but even then only if there are no criteria on indexed fields, or in
cases where you are sorting or selecting on an expression that has
to be processed client side AND have no criteria selecting on
indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1861
by: Michael Williams | last post by:
Hi, I am trying to understand the performance implications of running a number of separate ActiveXexe processes as opposed to a single ActiveXexe with multiple threads on a Windows 2000 server. I have been told that Windows does not allocate a fixed memory space to its processes, so they are always paged off to disk when inactive. However, all the threads in a single process operate within the same memory space, so are relatively...
5
509
by: rc | last post by:
Hi We have a SQL server on Win2k. the physical size of the db is about 40G and the main table has approx 65m rows in it. At the moment the entire database is on one data file. The entire server including the OS is on a RAID 0 array with one RAID controller. My question is would I get any performance benefit if I was to have more than several data file rather than one big data file, bearing in mind that there is only one disk...
25
4342
by: JW | last post by:
I'm putting together a bulletin board system for a community that allows folks to put up for sale postings, notices, etc. These notices usually include the posters' email addresses. A similar posting service which I use is in place in another town and I do occasionally get spam from my notices (not a lot, usually 419 spam.) This is a free system and doesn't require a login, and I want to avoid/minimize the possibility of spammers from...
2
1591
by: Terry | last post by:
when using one form - i can set the z-order. Is there a way programatically to create a "z-order" across .exe. I have 2 .exe - one that paints a background form and then another that fills in the middle of the screen on top of that form. The .exes need to be separate, although the middle .exe knows about the size of the outer background/border form via a remoted object and events that get triggered on resize. Everything is working...
12
11674
by: Steven T. Hatton | last post by:
I know of a least one person who believes std::ifstream::read() and std::ofstream::write() are "mistakes". They seem to do the job I want done. What's wrong with them. This is the code I currently have as a test for using std::ifstream::read(). Is there anything wrong with the way I'm getting the file? #include <vector> #include <iomanip> #include <fstream> #include <iostream>
4
2956
by: Mountain Bikn' Guy | last post by:
I need some advice on this. I am working on a fairly complex calculator app (C#) with lots of functions (and these functions in turn use math functions from an unmanaged C DLL). A calculation takes a lot of time (up to hours), and should run on a separate thread from the one that the GUI uses. The GUI also needs to display various properties for each function (such as parameters that can be set). It does this with property grid and other...
8
3419
by: Michael C | last post by:
Anyone have any hints on improving the performance of C# UI? I'm filling a TreeView and ListView with information returned by a SQLDataReader and information read from the Registry. I'm working on improving the SQL Query execution speed, but have no idea how to improve Registry-reading performance or TreeView/ListView update speed. Any ideas appreciated. Thanks, Michael C.
2
1349
by: rdudejr | last post by:
Hi all, I was doing some research on tuning sheapthres and sortheap and ran accross this at the boulder public lib: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005404.htm In here it states: ____________________________________________________________________________ You can use the database system monitor and benchmarking techniques to help set the sortheap and sheapthres...
2
2821
by: teo | last post by:
I need to use the 'Session_End' event in the 'Global.asax' file. It only works if in the 'Web.config' file the 'sessionState mode' Tag is present and its value is set to "InProc" 1) I thought it was a default setting, wasn't it?
16
3755
by: Matthew Zhou | last post by:
I am a students learning programming, and want to do some software projects to practice myself. However, no one will only use one language to make all the tasks done. And every languages has its strong and relatively weak side. So, what about C? Many friends of mine suggest me move to C++ or Java. But I there must be some places reserved for C (although they say C++ can do all the jobs of C).
0
9827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9677
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10862
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10262
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5838
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4468
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4033
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3119
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.