By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

How to reduce the response time

P: n/a
Dear reader,

I have an Access application which works as back-end and front-end.

In case it's running on a local PC it works perfect.

If I install it on a server the response time is increasing drastically.

Is there any one who knows how to reduce the response time to an acceptable
level in case the application is running on a server.

Tanks for any suggestion.

Kind regards,

Simon
May 10 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Simon" <Sv********@Versatel.nlwrote
... Access application ... back-end and front-end.
In case it's running on a local PC it works perfect.
If I install it on a server the response time is
increasing drastically.
Data that you were retrieving from your local hard drive is now coming
across the network... the back end is used just as if it were on a local
hard drive, but the transfer rate is much, much slower. In a file-server
database such as Jet or ACCDB, all the retrieval, manipulation, etc. code
executes on the user's machine.

There are several areas to address. Two main ones are are (1) optimize the
amount of data being retrieved across the network, e.g., indexing your data
on fields used to select records may mean that you only have to bring across
the index to determine the one/few records you actually need; limiting the
number of records to only those needed, esp. in combination with the above,
will reduce amount of data brought across (2) mimimize the overhead, e.g.,
keeping a connection open will eliminate the need to make and break a
network connection (Windows function, not Access function) each time you
access the back-end and (3) (related to 1.) be sure to put a copy of the
front-end on each user's machine so that not every database object you use
will have to be fetched across the network, e.g., queries, forms, reports,
macros, and modules should be in the front-end on the user's machine.
Is there any one who knows how to reduce
the response time to an acceptable level in
case the application is running on a server.
There are more, and detailed, suggestions for improving performance in the
multiuser environment (and on preventing database corruption, too) at MVP
Tony Toews' site http://www.granite.ab.ca/accsmstr.htm.

There is a wealth of additional information at http://www.accessmvp.com,
too.

Larry Linson
Microsoft Access MVP
May 10 '07 #2

P: n/a
"Simon" <Sv********@Versatel.nlwrote in
news:46*********************@news.tele2.nl:
Is there any one who knows how to reduce the response time to an
acceptable level in case the application is running on a server
There is one rule that applies in all applications:

Retrieve only as much data as the user needs for each operation.

If you do that, your app will be responsive in all environments in
which it runs.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '07 #3

P: n/a
ARC
That is a great rule, but I'm not sure how much it applies to the jet
database engine. Unfortunately, when you run a query on jet, it sends the
entire table (or all entire tables contained in a query) accross the wire to
the workstation, then runs the query there. So you still get the big
overhead of moving all data in the query tables accross the wire. Of course,
SQL sends the results only, and the query is ran on the server. That's one
thing I need to look into, to find out how hard it would be to re-do a large
app I have so that it runs in SQL.
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"Simon" <Sv********@Versatel.nlwrote in
news:46*********************@news.tele2.nl:
>Is there any one who knows how to reduce the response time to an
acceptable level in case the application is running on a server

There is one rule that applies in all applications:

Retrieve only as much data as the user needs for each operation.

If you do that, your app will be responsive in all environments in
which it runs.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

May 11 '07 #4

P: n/a
"ARC" <an**@andyc.comwrote
. . . Unfortunately, when you run a query on
jet, it sends the entire table (or all entire tables
contained in a query) accross the wire to the
workstation, then runs the query there.
That is not necessarily true; Jet treats the MDB file in the shared folder
the same as it would a file on the local hard disk. It retrieves only what
it needs to accomplish the task. If you have properly indexed your tables,
and are using a WHERE clause that refers to the indexed field, it will
retrieve the index, locate the desired record(s), and only bring back
that(those) record(s).

Only if you haven't indexed, or aren't using the index as your criteria, or
your criteria is a calculated field will it bring back as much data as you
imply that it always will. That's why, as one of my wise consultant friends
is fond of saying, "You've gotta know what you're doing."

There is at least one book dealing with how the Jet DB engine worked (in the
particular version about which the book is written) that is still largely
accurate. That is, some details have changed, but not the basics of how Jet
works. It was published in 1996, by Microsoft Press, and is by Haught,
Ferguson, et al. If you are interested, you'd probably have to find a used
copy... I seriously doubt it is still in print, eleven years later.

In using Access since 1993, I think every Access client to server DB
application with which I've worked was been made client-server for
reliability, logging, and recoverability -- not because of performance, nor
number of users. Some are, I'd guess, but more are converted because of fear
of performance problems than because of encountering performance problems.
Corporate IT departments are often the "fearmongers."

Larry Linson
Microsoft Access MVP
May 11 '07 #5

P: n/a
"ARC" <an**@andyc.comwrote in
news:Ti***************@newssvr19.news.prodigy.net:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"Simon" <Sv********@Versatel.nlwrote in
news:46*********************@news.tele2.nl:
>>Is there any one who knows how to reduce the response time to an
acceptable level in case the application is running on a server

There is one rule that applies in all applications:

Retrieve only as much data as the user needs for each operation.

If you do that, your app will be responsive in all environments
in which it runs.

That is a great rule, but I'm not sure how much it applies to the
jet database engine.
It's probably more important with Jet than with a server back end.
Unfortunately, when you run a query on jet, it sends the
entire table (or all entire tables contained in a query) accross
the wire to the workstation, then runs the query there.
This is simply FALSE.

Jet is *much* smarter than that.

The only case in which it does what you say is when the designer of
the database is VERY VERY DUMB and has not provided either a primary
key or indexes on the fields being used to select the requested
records.

Jet first retrieves only as many index pages as needed to locate the
data pages that contain the requested records, and then based on
what's found in the index pages, requests the specific data pages
needed. While that's certainly not as efficient as a database
server, it's many, many times more efficient than a table scan.
So you still get the big
overhead of moving all data in the query tables accross the wire.
You are simply mistaken as to how Jet works.
Of course,
SQL sends the results only, and the query is ran on the server.
That's one thing I need to look into, to find out how hard it
would be to re-do a large app I have so that it runs in SQL.
The kinds of changes that will make it efficient with a server back
end will also vastly increase the app's efficient with a Jet back
end.

Which is what I said on the front end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 12 '07 #6

P: n/a
"Larry Linson" <bo*****@localhost.notwrote in
news:0I61i.43$145.2@trnddc02:
Corporate IT departments are often the "fearmongers."
And often for precisely the reason that our present correspondent
incorrectly badmouths Jet -- they simply haven't a clue how Jet
actually works.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 12 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.