Speeding Up App Across LAN?

[(PeteCresswell)]

I just A/B'd my app:
------------------------------------------------------------
- Front end on C in both cases

- Back end on C in one case

- Back end on a LAN file server in the other case.

- Single user - i.e. nobody else was running the app
in either case.

- Off hours (a little after 8 pm in a corporate environment).
(i.e. one would hope minimal competing traffic and almost
surely much less streaming video bouncing around)
------------------------------------------------------------


Roughly speaking, the LAN-backend trial's response times were
twice as long as the C-backend trials.


Questions:
------------------------------------------------------------
1) Is there room for improvement here? Or is that pretty much
the way it is in most LAN environments? (i.e. a factor of 2,
maybe a little less... as in 1.7 or so)

2) If there is room for improvement, how?
------------------------------------------------------------


I tried to sell these guys on a SQL Server back end on the first
app I wrote for them - but they declined, citing a desire not to
be dependent on an outside group for care and feeding of the DB.

Not encumbered by any real LAN-type knowledge, I'm wondering if
there's room for a local file server here. I'm thinking that
maybe the existing "LAN-backend" situation involves more than
just a couple of router hops between the user's PC and the file
server - and I'm thinking maybe a file server in the same area
might speed things up. Anybody know?

 

[Tony Toews [MVP]]

I just A/B'd my app:

A/B'd your app? Do you mean you split the app?

1) Is there room for improvement here? Or is that pretty much
the way it is in most LAN environments? (i.e. a factor of 2,
maybe a little less... as in 1.7 or so)

2) If there is room for improvement, how?

The three most common performance problems in Access 2000 or newer
are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

This tips will never quite get it as fast as local. However that's as
good as you are going to get.

I tried to sell these guys on a SQL Server back end on the first
app I wrote for them - but they declined, citing a desire not to
be dependent on an outside group for care and feeding of the DB.

SQL Server is not necessarily a faster solution.

Not encumbered by any real LAN-type knowledge, I'm wondering if
there's room for a local file server here. I'm thinking that
maybe the existing "LAN-backend" situation involves more than
just a couple of router hops between the user's PC and the file
server - and I'm thinking maybe a file server in the same area
might speed things up. Anybody know?

Doubtful that would make a significant difference.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

Hi Pete,

In June, 2006, I did a lot of performance testing on a split Access
application at my place of work, using similar conditions (ie. late in the
evening, when network traffic would normally be expected to be fairly low). I
also shut down all applications that I was able to close, including Outlook,
to minimize interferance with the tests. However, I didn't really think using
the back-end DB on the local hard drive, since virtually 100% of the apps. I
work with at my place of work are small multi-user apps. I gave a
presentation to the PNWADG (Pacific NW Access Dev. Group www.pnwadg.org) in
June, 2006 showing the results of the tests I ran. If you'd like to have a
look at this presentation, you can download a copy here:

June, 2006 PNWADG Presentation on using JET ShowPlan
http://www.accessmvp.com/TWickerath/downloads/query_timing_results.zip

I also invite you to have a look at my Multi-User Applications paper,
available here:
http://www.accessmvp.com/TWickerath/

That said, if you are able to achieve a factor of approx. 1.7 to 2, I'd say
you are doing pretty darn good. A network is going to be the choke point.
Check out the section of my Multi-User app. on using JETShowPlan.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[(PeteCresswell)]

Per Tony Toews [MVP]:

This tips will never quite get it as fast as local. However that's as
good as you are going to get.

Can anybody offer up a percent to shoot for? 80% as fast as
local? 75? .... Seems like I'm already getting fifty - at
least at night with single user.

 

[(PeteCresswell)]

June, 2006 PNWADG Presentation on using JET ShowPlan
http://www.accessmvp.com/TWickerath/downloads/query_timing_results.zip

I also invite you to have a look at my Multi-User Applications paper,
available here:
http://www.accessmvp.com/TWickerath/

That said, if you are able to achieve a factor of approx. 1.7 to 2, I'd say
you are doing pretty darn good. A network is going to be the choke point.
Check out the section of my Multi-User app. on using JETShowPlan.

Thanks!...

I'll start digging into those two docs ASAP.

I already see a reference on your web page to laptops and
wireless connections that may explain one of my user's redcurrant
"Network Error" errors that I've been unable to provoke on my PC.

 

[(PeteCresswell)]

RE/ KB303528:
---------------------------------------------------------------
"Minimize the number of connections that are made from each
client We recommend that you design each client to use one
connection to a Microsoft Jet database. Each connection to a Jet
database represents an independent client to the database, even
when these connections come from the same client process.

To optimize performance and network I/O and to reduce the
multi-user stress on the back-end database, design the client
application to use a single connection to the Jet database. Share
this connection over multiple record sets as required. This has
the added benefit of preventing read delays and write delays in
the client application. By default, there is a five-second delay
between writing a value to the database and being able to read
this updated value when writing and when reading on two different
Jet connections. This is true even if the two connections reside
in the same client process. If you use a single connection, you
avoid this issue."

 

[David W. Fenton]

Not encumbered by any real LAN-type knowledge, I'm wondering if
there's room for a local file server here. I'm thinking that
maybe the existing "LAN-backend" situation involves more than
just a couple of router hops between the user's PC and the file
server - and I'm thinking maybe a file server in the same area
might speed things up. Anybody know?

Not likely, unless the people designing the LAN are spectacularly
incompetent.

You don't give the magnitude of the performance difference, only the
ratio, so it's not clear that it matters. Something that takes 50
milliseconds on the local drive and 100 on the server is not
something I'd be worrying about.

If it's a problem of the magnitude that it *does* make a difference,
then you have to examine the way you're retrieving the data, how
much you're retrieving at once, and decide if it can be done more
efficiently.

One of the most important issues, I've recently found, is outer
joins. If you have foreign keys that can be Null and you put those
tables in queries with an outer join, you can vastly slow down your
application in some cases. Making the FK required (and the join an
INNER JOIN) can speed up these queries enormously (either with Jet
or with SQL Server, for instance).

 

[David W. Fenton]

Per Tony Toews [MVP]:

Can anybody offer up a percent to shoot for? 80% as fast as
local? 75? .... Seems like I'm already getting fifty - at
least at night with single user.

It depends entirely on what you're doing. Some operations are going
to be noticeably slower when you have a connection that is 1/10th or
less the speed of your local database. All you can do in that case
is try to re-engineer the task that is bogged down by the network or
live with it.

One of the best ways to do that is to make sure you've got criteria
appropriately placed in your queries. You could run SHOWPLAN to see
what's up and then perhaps diagnose the places where you could
optimize.

 

[David W. Fenton]

I already see a reference on your web page to laptops and
wireless connections that may explain one of my user's redcurrant
"Network Error" errors that I've been unable to provoke on my PC.

You can't run an Access app across a wireless connection, even a
54mpbs one (which is sufficient bandwidth), because the connections
drop out way too often.

 

[David W. Fenton]

I already see a reference on your web page to laptops and
wireless connections that may explain one of my user's redcurrant
"Network Error" errors that I've been unable to provoke on my PC.

BTW, WiFi is a slam-dunk for upsizing the back end to SQL Server.
SQL Server will do just fine, while Access will croak often, perhaps
even corrupting data. If wireless is required, you just can't use a
Jet back end, period, end of statement.

 

[(PeteCresswell)]

Per David W. Fenton:

You don't give the magnitude of the performance difference, only the
ratio, so it's not clear that it matters. Something that takes 50
milliseconds on the local drive and 100 on the server is not
something I'd be worrying about.

If it's a problem of the magnitude that it *does* make a difference,
then you have to examine the way you're retrieving the data, how
much you're retrieving at once, and decide if it can be done more
efficiently.

It's on the order of .6 seconds vs 1.2 or 1.5 seconds.

 

[Paul Shapiro]

You don't have any control over the connection(s) that Access uses
internally. This is only applicable to your own VBA code, or your non-Access
application working against an Access db.

Since you're calling the server a LAN-based server, it seems doubtful that
server latency is an issue, but it should (sometimes!) be pretty easy to
check the configuration with your network people.

I had Access applications performing miserably on a switched local LAN with
a new computer. Updating the network interface driver made all the
difference in the world. Peformance now feels almost the same as a local
back-end db, like it did on the other computers.

 

[Tom Wickerath]

I agree completely with David on this issue.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[David W. Fenton]

Per David W. Fenton:

It's on the order of .6 seconds vs 1.2 or 1.5 seconds.

What operations does it occur in that it matters?

 

[David W. Fenton]

This doesn't compute for me in the context of ODBC.

What does ODBC have to do with it? Are you saying you were running
your database server locally vs. across the LAN, and you saw these
differences? Or were you using a Jet back end? I assumed a Jet back
end in all my answers, but your comment about ODBC makes me think
that was an erroneous assumption on my part.

 

[(PeteCresswell)]

Per David W. Fenton:

What does ODBC have to do with it? Are you saying you were running
your database server locally vs. across the LAN, and you saw these
differences? Or were you using a Jet back end? I assumed a Jet back
end in all my answers, but your comment about ODBC makes me think
that was an erroneous assumption on my part.

Jet back end.

I just can't figure out what the KB article means by "number of
connections". Concurrent open recordsets?

 

[(PeteCresswell)]

Per David W. Fenton:

What operations does it occur in that it matters?

Populating the details on a screen.

Maybe this makes me a closet Type-A, but I find anything over
sub-second response time tb irritating - and I'm assuming that
most of my users are similarly affected.

 

[David W. Fenton]

Per David W. Fenton:

Jet back end.

I just can't figure out what the KB article means by "number of
connections". Concurrent open recordsets?

If you use a cached reference to your back end database, each
workstation should be using a single connection.

I think, though, that the article is written with ADO in mind.

I don't see that the quote you gave is likely to have any effect on
your problems.

 

[Tony Toews [MVP]]

Can anybody offer up a percent to shoot for? 80% as fast as
local? 75? .... Seems like I'm already getting fifty - at
least at night with single user.

75% for most things. Complex forms with subforms and combo boxes
slower. Reports with lots of subreports could get 50 times slower if
they are say a hundred pages with many subreports per page.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

BTW, WiFi is a slam-dunk for upsizing the back end to SQL Server.
SQL Server will do just fine, while Access will croak often, perhaps
even corrupting data. If wireless is required, you just can't use a
Jet back end, period, end of statement.

Agreed. That's been mentioned a number of times as a cause of
corruptions in the newsgroups.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/