Access Split Performance Problem

From: Julian (Julian_at_discussions.microsoft.com)
Date: 03/04/05


Date: Fri, 4 Mar 2021 04:49:16 -0800

Hi,

The problem is as follows:
A split database exists with the back end on a win2k3 file server. Shares
and directory names meet short naming standards. The front end resides on a
winXP / win2k machine. (As per KB articles 843418 and 814112)

At various points in time, I have tried all the points laid out in KB 889588
(various reg hacks and suggestions like persitant recordsets in the front end
on opening the DB), with the exception of upgrading JET as I am already using
the latest version. In addition I have tried disabling oplocks on the server
and SMB signing on the server.

However, my suspicion of network problems being the cause are not solid for
reasons set out in scenario 2.

History: This seems to have developed since moving the backend from an NT4
server share. The database is propogated yearly, cleaned out, regularly
compacted and the drive is defragmented.

Notable design points: The backend has a lot of relationships, queries are
nested to up to three levels and there are on average no more than 3000
records.

This problem does not affect single table queries, it *does* affect queries
where joins are present, it also affects unjoined tables (via SQL) but where
a column is output (ie a join is created implcitly). If a second table is in
the design view but no columns are selected (From statement eg, FROM X,Y),
this problem does not occur

Scenario 1:
Open front end, open query in design view. Time to open: 1min 5s
Close the query within 5s and it generally opens in 20s. After 5s it
generally takes 1min 5s again. These times are **consistent** and I believe
this is significant.

Analysis: It looks to me like the quick opening times are cached in some
way, but, why after 5 of being open is that cache seemingly lost and why the
consisten time of 1min 5s.

I have run processor and network traces on the server and client during this
time and both show no activity other than spikes on request and delivery of
the information.

Scenario 2:
Open the backend on the client via a mapped drive or UNC. Copy the query
opened in the front end to the backend and open it in design view.
Time to open: instant.

Analysis: this suggests that networking is not the cause of the problems. As
also suggested by the lack of problem fix from the KB article suggestion.

I am absolutely stuck on this as I can find no help or discussion like this.
If anyone can help I would be very very grateful. Please feel free to contact
me via email or IM.

Kind Regards

JS (Dreamcurve@hotmail.com)