Split Database Very Slow

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.

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

HTH

Vlado

píse v diskusním príspevku
ups.com...
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.

You have two issues he

1) - check your setup, and a list of things go help fix performance he
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 he
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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" píše v diskusním
pøíspìvku ...
You have two issues he

1) - check your setup, and a list of things go help fix performance he
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 he
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Vladimír Cvajniga" wrote in message
...
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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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" píše v diskusním
pøíspìvku ...
"Vladimír Cvajniga" wrote in message
...
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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Vladimír Cvajniga" wrote in message
...
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...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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" píše v diskusním
pøíspìvku ...
"Vladimír Cvajniga" wrote in message
...
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...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Vladimír Cvajniga" wrote in message
...
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).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Albert D. Kallal" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/