Split Database Very Slow

[charles.kendricks]

I have a split database with the BE on a new Win Server 2003 R2
server, which is configured as a domain controller, and the FE's on
WinXP workstations. The workstations are not configured into the
domain as of yet (some of the stations still need to be upgraded to
WinXP Pro). The BE is just sitting on a shared drive which the
workstations have access to. I have workstations both on the local
network and on remote networks. All have private IPs utilizing DHCP
supplied from their various routers. My problem is very, very slow
response time from the server, especially when accessed from outside
the local network. I'm sure my network architecture is part of the
problem, but I'm not sure what all I should be doing to correct the
problem, or anything else that might be causing the slow response.

 

[Vladimír Cvajniga]

One of the reason: default settings, see
http://support.microsoft.com/kb/150384/.

HTH

Vlado

 

[Albert D. Kallal]

You have two issues here:

1) - check your setup, and a list of things go help fix performance here:
http://www.granite.ab.ca/access/performancefaq.htm

From the above list, the top one for me that works is a persistent
connection. this is mentioned in the above link. (this means that the front
end should keep the back end opened at all times).


2) Your outside users...that sounds like a WAN, not a LAN. I mention the
problems (and fixes) with a WAN here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Vladimír Cvajniga]

I strongly disagree with 1). I have tried it in one of our installations
(>20 PCs in LAN) but it was resulting in surprisingly slow startup of the
application when there were an open connection from another LAN station. So
I decided to remove the persistent connection and modify server's registry
according to http://support.microsoft.com/kb/150384/. Start-up time has
dropped down by more than 15-20 times!!! And the application's response is
much faster in the whole.

Vlado

 

[Albert D. Kallal]

I strongly disagree with 1). I have tried it in one of our installations
(>20 PCs in LAN) but it was resulting in surprisingly slow startup of the
application when there were an open connection from another LAN station. So
I decided to remove the persistent connection and modify server's registry
according to http://support.microsoft.com/kb/150384/. Start-up time has
dropped down by more than 15-20 times!!! And the application's response is
much faster in the whole.

What about when a is working on the database? If any user is working on the
database, and they have a form open, then they have a persistent
connection..don't they?

Further, do some testing with a persistent connection. Even after that reg
modifying, you find your application performs better..in most cases ..much
better.....

Do some testing here. If you open a table, and ms-access has to check or
create the ldb locking file each time, you will get delays. By forcing the
connection open, you eliminate this delay each time. The fact that you
modify the registry to reduce this delay still does not eliminate it. You
should continue to keep that persistent connection, and even AFTER you done
that reg mod,, you will continue to receive BETTER performance then what you
obtain now....

that reg mode will reduce the delay...but, that delay will still exist..and
the persistent connection will still eliminate the delays associated with
checking for, and trying to delete, and trying to create the ldb locking
file. The fact that you done a reg mode to reduce this delay still does not
mean the persistant connectil will not help your case furhter...in most
cases...it will help.

 

[Vladimír Cvajniga]

IMHO, the creation of LDB is a matter of miliseconds... even on a server.
The only issue is BE MDB deep directory nesting and non 8.3 file-name when
checking user's rights - this may prolong the access attempt. You can reduce
an access to LDB if you put it to root directory and give it 8.3 name (don't
use long file-names).
Registry settings: if you reduce 5x200 miliseconds, eg. 1 second, to zero
seconds (on a simple database access), it will fasten your application for
sure. Especially if you have a "heavy" BE DB initialization.

Vlado

 

[Albert D. Kallal]

IMHO, the creation of LDB is a matter of miliseconds... even on a server.

Ah, but it not always. Further, ms-access often tries to get exclusive
rights to the file, and then if it can't, it
falls back to multi-user mode.

Sometimes, the time taken to grab that file, and build the ldb file can be
considerable.

By not keeping a persistent connection open, then you slow down the opening
of a table to that of the server OS abilities to create that file.

You much better off to force this creating and opening process to occur ONLY
once time.

this persistent trick even works when you have NO network, and are running a
split database on your local drive.

It is a good, and time honoured practice that just about everyone here over
time has learned that it is worth while benefit.


You telling me that you want the JET database engine to slow down, and WAIT
to the speed of the file systems on the other end
to create a file.

I am telling your that by opening the back end ONCE, you eliminate this
overhead, and thus


Try the folwling code:

Dim r As dao.Recordset
Dim t As Double
Dim i As Long
Dim dbPersistent As dao.Database

' Set dbPersisten = OpenDatabase(strBackEnd)
'
t = Timer
For i = 1 To 20
Set r = CurrentDb.OpenRecordset("contacts")

r.Close
Set r = Nothing
Next i

t = Timer - t
Debug.Print t

The following takes
1.344 seconds

Then I comment in the
Set dbPersisten = OpenDatabase(strBackEnd)

I run the code again, and, the time is half:

..64 seconds

That is 1/2 the time!!! So, that open costs time.

in some cases, the slowdown is even more dramatic.

Your mileage will vary, but you will find that keeping a persistent
connection open will even help those systems that run ok, and will run even
better.

It is not a must do, but tends to help applications a lot....

Try the above loop code on your split system over your network. It should
help...

 

[Vladimír Cvajniga]

As I wrote, we have better results without persistent connection, especially
on application's start-up. Start-up was much slower with persistent
connection so that I finally gave up and removed it.

Start-up with persistent connection (or with an open BE DB recordset)
without modified registry settings:
- ~90 seconds or more depending on open applications (100Mbit LAN)

Start-up without persistent connection (with an open BE DB recordset from
different PC) with modified registry:
- less than 10 seconds

These results were sent to me from an admin on that appropriate installaton.
It was about two months ago so it's still resides in my memory ;-). Believe
me, he is really good in his job. We made several attempts to tune the
perfomance up until the admin finally found what we were searching for:
http://support.microsoft.com/kb/150384/.

I must not forget: it's a A97 application, BE MDB is located on Win2003
server with modified registry, clients' FEs are on Windows XP.

Vlado

 

[Albert D. Kallal]

As I wrote, we have better results without persistent connection,
especially on application's start-up. Start-up was much slower with
persistent connection so that I finally gave up and removed it.

Yours would be the first documented case of a persistent connection slowing
down
a start-up. I suspect that the persistent connection was not being done, or
the user
miss-understood completely as to how to setup a persistent connection.

Until I see how they actually set this up, I simply don't believe this. Why
would
opening up ONE table cause such a huge delay? It would not. If that start-up
code
creates a persist connection, it only going to occur once, and after that
there
would be at a the WORST a zero increase in additional overheard. And, 99%
of the time it will improvement things.

These results were sent to me from an admin on that appropriate
installaton. It was about two months ago so it's still resides in my
memory ;-). Believe me, he is really good in his job.

Good at what job? Does he know ms-access well? Does he know how to
setup a persistent connection?

I call this information complete crap, and the person who gave you that
information is just plain wrong, or does not know how to set this up, or
the application was not split (or, the front end was not placed on each pc).

Until I see hard documenting that the persistent connection was setup
correctly, and that the use of a persistent connection acutely
slowed down this appcation start-up compared to a NON persistent connection,
I don't simply believe this information.
The information you received from that person is wrong and based on their
lack of knowledge on this issues.

Plain and simple...

The only possible way a increase in start-up time would occur is if the old
existing start-up did not open, or access any of the backend
table. However, that being the case, then there would be no delay in
start-up at all anyway.

What is being reported here does not make sense, and until I see better
document of this case, it not going to win any discussion.

I realize that you can only report what was being told to you, and I realize
that what is being told to you is likely what they experienced. however, not
knowing the details of the setup, ad how the persistent connection was being
made..I simply don't believe they knew what they were doing, and they did
not setup the persistent connection correctly (it was likely going in, and
out of scope, or the persisction conenciton was being created over and
over...it MUST occur ONLY once for this to work correctly).

 

[David W. Fenton]

It is a good, and time honoured practice that just about everyone
here over time has learned that it is worth while benefit.

Not me. I never use it.

But then, in a practical sense, I actually am, because I tend to use
a global db variable function and keep some bound forms open but
hidden.

I've only once implemented the persistent connection approach and it
didn't make any noticeable difference.

 

[David W. Fenton]

I must not forget: it's a A97 application, BE MDB is located on
Win2003 server with modified registry, clients' FEs are on Windows
XP.

A97/Jet3.5 was completely different from the Jet 4-based versions of
Access. Access 2000 (with Jet 4) introduced substantial performance
issues that Jet 3.5 did not have.

 

[David W. Fenton]

Until I see hard documenting that the persistent connection was
setup correctly, and that the use of a persistent connection
acutely slowed down this appcation start-up compared to a NON
persistent connection, I don't simply believe this information.

It's also not clear how "startup" is being defined.

You would be defining startup as including the opening of the first
bound form. With no persistent connection, the overhead of creating
the LDB becomes part of opening that first form (and every other
form in cases when a bound form is opened when there are no longer
any other bound forms open).

Your correspondent seems to be ignoring this and not measuring the
amount of time it takes to get the first form open. Of *course* the
non-persistent connection is going to be faster in that scenario.

The other issue is that the tests were for A97/Jet 3.5 not the later
versions of Access (using Jet 4.0), and my experience is that Jet
3.5 just didn't have any of the major performance issues introduced
with A2K.

 

[Albert D. Kallal]

I've only once implemented the persistent connection approach and it
didn't make any noticeable difference.

In am total agreement on this.

However, the persisting should not slow down the loading the application
either!! (unless as mentioned, the application don't connect to the back end
when it loads in the first place!).

And, I did have one client that experienced the slow down when using
access97. The persist connection fixed that a97 problem.

However, as you said, in a97, the slow down problem, the resulting fixing by
a persisting connect usually did NOT help.

In a97, it rarely changed anything. However, a slow down of performance due
to doing this is what is un-heard of.

 

[Vladimír Cvajniga]

miss-understood completely as to how to setup a persistent connection
I still have old (commented out) code that I had in main form:

in Form_Open:
'Set rsVzdy = CurrentDb.OpenRecordset("VZDY")

VZDY (ALWAYS in English) was a table in a shared BE MDB

in Form_Close:
'rsVzdy.Close
'Set rsVzdy = Nothing

I think it was enough for persistent connection. ;-)

We have removed it very long time ago (Jan 15th.2004) . And we use registry
settings for 14 months now (since . And I have a fresh e-mail with some test
results (~2 months old) from the admin gloryfying our new solution. That's
the way it is... :-/

Vlado

 

[Albert D. Kallal]

If you experienced a different result, then that is fine.

Remember, I just bit surprised. And, I have to thank you very kindly for
sharing your information.

You come here, open arms, and shared some of your experiences with
ms-access. So, I do respect you.

I hope you don't think my response was too harsh.

For someone with a fair, and honest attitude like you, you certainly not
here trying to make trouble.

You can only be reporting what you are experiencing. In this regards, I am
not disagreeing with what you are reporting.

So, I do take well note. You are making a nice effort to share your
experiance with ms-access, and for that I tip my
hat to you.

And, I thank you kindly for sharing the link to the KB article. I hope this
thread can help others with the same problem.

 

[Vladimír Cvajniga]

I do respect you too, Mr. Kallal. I do believe you may have fine results.
But we have experienced something completelly different. That's why I
couldn't just agree with you.

It took very long untill then admin finally found a solution. And, finally I
(personally) have changed registry setting on Windows 2003 server with our
BD MDB... a fortnight ago. So it's still fresh in my memory. It's a
significant difference.

I have reported:
1) We had poor respond from our app when we were using persistent
connection.
2) We have fixed a start-up problem when we removed persistent connection.
3) We have significantly speeded up app's response when we changed registry
settings.

That's why, at least for me, persistent connection in Access 97 as a tool to
tune up app's performance is a myth.

I tell the things as I feel them. I never fight - I'm a peaceful man. I
don't even have a gun!

In respect...

Vlado

 

[Vladimír Cvajniga]

P.S. Even without persistent connection there was slow response on start-up
if somebody has opened a BE recordset from another machine (ie. two or more
simultaneos BE MDB connections). And it's very hard to believe that nobody
has similar experience...

 

[Vladimír Cvajniga]

P.P.S.
We also had performace issues on computers with some earlier versions of AVG
(Grisoft Antivirus System). Grisoft was using "AVGDB.MDB datacenter type"
which was resulting desperatelly slow performance of Access applications. I
think they don't use it anymore.

 

[David W. Fenton]

In am total agreement on this.

However, the persisting should not slow down the loading the
application either!! (unless as mentioned, the application don't
connect to the back end when it loads in the first place!).

And, I did have one client that experienced the slow down when
using access97. The persist connection fixed that a97 problem.

However, as you said, in a97, the slow down problem, the resulting
fixing by a persisting connect usually did NOT help.

In a97, it rarely changed anything. However, a slow down of
performance due to doing this is what is un-heard of.

The one time I used it was an A2K app.

It did nothing that was noticeable.

 

[David W. Fenton]

I still have old (commented out) code that I had in main form:

in Form_Open:
'Set rsVzdy = CurrentDb.OpenRecordset("VZDY")

VZDY (ALWAYS in English) was a table in a shared BE MDB

Er, am I missing something, or is it not the case that without a
defined database object that recordset would go out of scope
immediately?

Secondly, you don't need to open a recordset to get the LDB file
created. All you need is to set a database variable pointing to the
back end file.