After splitting, terrible performance!

[David Portwood]

Access 2000: I just split my db using the Splitter utility. This was the
first time I tried to do something like this. Ended up with an app that
takes seconds to open a form or save a record, even when both fe and be are
locally resident!

I remembered reading about a potential fix to this problem involving keeping
an open connection to the be. So I changed my main Switchboard form so that
the title label is now a field bound to a table in the be rather than a
label. No difference in performance.

I also remember reading something about mapped drives. I didn't really check
for this, but when I browsed to find the proper location on the net to put
my be, I went to the "S:\" drive, as usual.

When I ran the unsplit app from the shared drive the performance seemed
fine.

I believe in the advantages of splitting the database and I know I must be
doing something wrong. Can anyone suggest a fix?

 

[Ken Snell \(MVP\)]

See these websites for more info about performance with split files:
http://www.granite.ab.ca/access/splitapp/index.htm
http://www.allenbrowne.com/ser-01.html

Normally, maintaining a persistent connection to the backend file makes the
biggest improvement in performance. This is done by opening a hidden form
when the file is opened, and having that form be bound to a table in the
backend file. When the application is closed, ACCESS will close this form
automatically.

 

[David Portwood]

As I said, I *have* a persistent connection to the backend. It's not hidden
form, but I don't see how being hidden or visible could make a difference.

The Allen Browne link contained nothing about performance that I could find.
The other link did not exist when I tried it.

 

[Albert D. Kallal]

The other link did not exist when I tried it.


Darn...it looks like tony's site is down....

Try that 2nd link tomrrow..it is a gem full of things to check.....

 

[Tony Toews [MVP]]

Darn...it looks like tony's site is down....

Should be up by 9 pm Mountain time Saturday.

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/

 

[David Portwood]

I just google for "split database performance" and found an article on p2p
where somebody describes exactly the same problem I've run into and the
response was - I kid you not - "unfortunately, that's the way access
responds in a networked environment".

What am I hearing? That Access just sucks on a network and if I split the db
this is what I'll just have to live with? This is very disturbing.

I have to deploy my app on Tuesday "come what may" and a performance hit
like this is unacceptable. I am seriously considering recombining the db
because the performance seems fine like that.

 

['69 Camaro]

Hi, David.

What am I hearing? That Access just sucks on a network and if I split the
db this is what I'll just have to live with? This is very disturbing.

You're hearing from inexperienced Access database developers who have been
unsuccessful at networking their database applications. If you made the
same design mistakes that they did, you'll get similarly poor performance.
You say that this is the first time you've split a database application, so
it's not surprising that you've discovered how slow it is when a single-user
database application is deployed as a multiuser database application. It
can be a real eye-opener when one discovers how the database should have
been designed from the beginning.

Are your tables normalized or are they designed for spreadsheet users
instead? Do the tables all have primary keys? Do related tables have
referential integrity enforced, but with a manually added index on the same
column(s)? Are your tables properly indexed? Are there unnecessary indexes
added to every ID, Code, and Key column (which is the default setting)?

Are your queries, forms, and reports bringing every column of every record
in a table across the network when only one record is required, or perhaps
only a few columns of the table are needed for the group of displayed
records? Are your queries using the IN operator in the WHERE clause,
instead of outer joins? Is your VBA code using Recordsets for data
manipulation, instead of SQL action queries?

Did you disable the default Name AutoCorrect? Did you change all
sub-datasheets to None?

Is your database file located in the root directory (or very close to it) on
the networked server and on your workstation, or did you bury it deep in the
directory structure, which requires the network to authenticate the user's
permissions at each directory level in the path when accessing the file?
Did you use the DOS 8.3 naming convention for every directory in the path
and the database file name, or did you name it "S:\Windows Must Call
Multiple Slow API Functions\Several Times\To Resolve This Name.mdb"?

I am seriously considering recombining the db because the performance
seems fine like that.

You can follow all the other Access database developers who were
unsuccessful at making their single-user database application into a
multiuser one, or you can read Access MVP Tom Wickerath's article,
"Implementing a Successful Multiuser Access/JET Application," on the
following Web page for some great tips to improve performance:

http://www.access.qbuilt.com/html/multiuser_applications.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Ken Snell \(MVP\)]

I just google for "split database performance" and found an article on p2p
where somebody describes exactly the same problem I've run into and the
response was - I kid you not - "unfortunately, that's the way access
responds in a networked environment".

What am I hearing? That Access just sucks on a network and if I split the
db this is what I'll just have to live with? This is very disturbing.

No, that is not how ACCESS behaves in a network environment. Trust me, many
of us in development community use split databases all the time.... in fact,
if you don't split the databases when you multiple users, you are asking for
corruption problems.

As for persistent connection to backend file, does the Switchboard form stay
open all the time while the application is in use?

 

[David Portwood]

Hi, David.


You're hearing from inexperienced Access database developers who have been
unsuccessful at networking their database applications. If you made the
same design mistakes that they did, you'll get similarly poor performance.
You say that this is the first time you've split a database application,
so it's not surprising that you've discovered how slow it is when a
single-user database application is deployed as a multiuser database
application. It can be a real eye-opener when one discovers how the
database should have been designed from the beginning.

I don't doubt that my design could be improved. Anybody's design could be
improved. However, when the app was unsplit I ran it from a network drive
and it ran very quickly. After I split the database, with exactly the same
code, it ran very slowly from the same network drive. This leads me to
believe it's the split and not my design.

Are your tables normalized or are they designed for spreadsheet users
instead? Do the tables all have primary keys? Do related tables have
referential integrity enforced, but with a manually added index on the
same column(s)? Are your tables properly indexed? Are there unnecessary
indexes added to every ID, Code, and Key column (which is the default
setting)?

My tables are keyed and normalized with no manually added indexes to keyed
columns.

Are your queries, forms, and reports bringing every column of every record
in a table across the network when only one record is required, or perhaps
only a few columns of the table are needed for the group of displayed
records? Are your queries using the IN operator in the WHERE clause,
instead of outer joins? Is your VBA code using Recordsets for data
manipulation, instead of SQL action queries?

Probably true, in certain instances. But, as I said before, the unsplit app
worked fine from a network drive.

Did you disable the default Name AutoCorrect? Did you change all
sub-datasheets to None?

No. I'll do these things.

Is your database file located in the root directory (or very close to it)
on the networked server and on your workstation, or did you bury it deep
in the directory structure, which requires the network to authenticate the
user's permissions at each directory level in the path when accessing the
file?

The _be is located 4 or 5 levels down on the network drive. I will fix this.
My fe is in My Documents.

Did you use the DOS 8.3 naming convention for every directory in the path
and the database file name, or did you name it "S:\Windows Must Call
Multiple Slow API Functions\Several Times\To Resolve This Name.mdb"?

Don't know what "DOS 8.3 naming convention" means. I created folders and
gave them names. So what is this naming convention and how can I use it to
speed up performance? I just read Wickerath's article. Foldernames should be
eight chars or less, right? I'll fix that.

You can follow all the other Access database developers who were
unsuccessful at making their single-user database application into a
multiuser one, or you can read Access MVP Tom Wickerath's article,
"Implementing a Successful Multiuser Access/JET Application," on the
following Web page for some great tips to improve performance:

I am looking at Wickerath's article now. There are some things I can check
and probably correct.

Thanks for the help, Gunny. If you think of anything else, please pass it on
to me.

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

Hi, David,

to speed the performace up you could also try
http://support.microsoft.com/kb/150384/.

I have Czech version of Access and I've found out that persistent
connection, for an unknown reason, does NOT help.
See
http://groups.google.co.uk/group/mi...own+performance&rnum=1&hl=en#7b97839a87e6d970.

Vlado

 

[David Portwood]

As for persistent connection to backend file, does the Switchboard form

stay open all the time while the application is in use?

Yes. The Switchboard allows the user to open modal forms in sequence while
the Switchboard form itself stays always open.

 

['69 Camaro]

Hi, David.

However, when the app was unsplit I ran it from a network drive and it ran
very quickly. After I split the database, with exactly the same code, it
ran very slowly from the same network drive. This leads me to believe it's
the split and not my design.

I deployed my first split application one night after work, and the very
first thing the next morning my phone was ringing off the hook. "What did
you DO?!! PUT IT BACK!!! PLEASE!!!" The speed was totally unacceptable.
I hadn't changed any code whatsoever, and the database application was in
the same exact location, with the same name. I had a whole bumper crop of
design mistakes to learn from that morning. I committed most of the
mistakes I listed in my first reply, although some of them were out of my
control, such as the deeply buried path and non-DOS 8.3 path and file name
convention. Everyone wants to blame the network for this slow performance,
but it's mostly due to the fact that the database application was designed
for a single-user, non-split database. When these applications are deployed
into a multiuser environment, the design flaws _really_ become evident by
the slow performance.

My tables are keyed and normalized with no manually added indexes to keyed
columns.

Good. Do you also have indexes on every column that includes ID, Code, or
Key in the name? The default is to assign an index to each of these
columns, even if there's already an index on them from the primary key or
hidden index on the referenced columns in a foreign key constraint. The
extra, unnecessary indexes can slow down your database application.

But, as I said before, the unsplit app worked fine from a network drive.

A lot of the design flaws are hidden until the database application is
split.

My fe is in My Documents.

The standard path to My Documents is C:\Documents and Settings\<User
Name>\My Documents, so that's going to be a slow path, because the operating
system has to call the API function for long file names to resolve the name
on each of those directories, in addition to calling the network API
function to check whether the user has permission to read each of the
directories in the path to the file.

Don't know what "DOS 8.3 naming convention" means. I created folders and
gave them names. So what is this naming convention and how can I use it to
speed up performance?

In the days of DOS, directories and file names couldn't exceed eight
characters, and they could only use up to three characters for the
extension. Also, only alphanumeric characters and the underscore character
could be used. Windows supports long file names, but it uses a slow Windows
API function to resolve the name if it doesn't conform to the DOS 8.3
standard. So, if any of the directories in the path contain a space
character, you'll have to wait for the Windows API function to be called to
figure out the real name of the directory. If you use camel case and a
reasonably short name, a human will still be able to read the name, and your
operating system won't have to call extra API functions, which slow down
file access.

If you think of anything else, please pass it on to me.

Ensure that your antivirus software isn't scanning for MDB files. And that
goes for MDE and MDA files, too. Use optimistic record locking ("No
Locks"), not pessimistic record locking, for the default record locking
database option and the forms. Use the row level locking database option,
not page level locking. This is set by the first user to open a connection
to the back end, so ensure that all users have this setting, because if the
first user opens the back end database with page level locking, all the
other users who subsequently connect will be using page level locking until
the database if finally exited by all users and the LDB file is deleted.
And ensure that each user has a copy of the front end on his own
workstation, not a shared front end file on the network.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Ed Metcalfe]

Access 2000: I just split my db using the Splitter utility. This was the
first time I tried to do something like this. Ended up with an app that
takes seconds to open a form or save a record, even when both fe and be
are locally resident!

I remembered reading about a potential fix to this problem involving
keeping an open connection to the be. So I changed my main Switchboard
form so that the title label is now a field bound to a table in the be
rather than a label. No difference in performance.

I also remember reading something about mapped drives. I didn't really
check for this, but when I browsed to find the proper location on the net
to put my be, I went to the "S:\" drive, as usual.

When I ran the unsplit app from the shared drive the performance seemed
fine.

I believe in the advantages of splitting the database and I know I must be
doing something wrong. Can anyone suggest a fix?

If performance is poor even when the frontend and backend both reside on the
local drive could this perhaps be a corruption in the table indexes? I'd try
a compact and repair on both the frontend and backend databases. If this
fails I'd try importing all the objects into a new MDB - this forces a
rebuild of all indexes.

If the performance acceptable if you put the unsplit database on the network
drive?

Ed Metcalfe.

 

[David Portwood]

Good. Do you also have indexes on every column that includes ID, Code, or

Key in the name? The default is to assign an index to each of these
columns, even if there's already an index on them from the primary key or
hidden index on the referenced columns in a foreign key constraint. The
extra, unnecessary indexes can slow down your database application.

No unnecesary indexes.

The standard path to My Documents is C:\Documents and Settings\<User
Name>\My Documents, so that's going to be a slow path, because the
operating system has to call the API function for long file names to
resolve the name on each of those directories, in addition to calling the
network API function to check whether the user has permission to read each
of the directories in the path to the file.

I'll fix this.

In the days of DOS, directories and file names couldn't exceed eight
characters, and they could only use up to three characters for the
extension. Also, only alphanumeric characters and the underscore
character could be used. Windows supports long file names, but it uses a
slow Windows API function to resolve the name if it doesn't conform to the
DOS 8.3 standard. So, if any of the directories in the path contain a
space character, you'll have to wait for the Windows API function to be
called to figure out the real name of the directory. If you use camel
case and a reasonably short name, a human will still be able to read the
name, and your operating system won't have to call extra API functions,
which slow down file access.

I will move the db's to the root directories.

Ensure that your antivirus software isn't scanning for MDB files. And
that goes for MDE and MDA files, too.

I don't have any control over the antivirus software. I will email someone
about scanning for MDB files, but I doubt if my desires will have much
influence.

Use optimistic record locking ("No

Locks"), not pessimistic record locking, for the default record locking
database option and the forms.

I am doing this now.

Use the row level locking database option, not page level locking. This is
set by the first user to open a connection to the back end, so ensure that
all users have this setting, because if the first user opens the back end
database with page level locking, all the other users who subsequently
connect will be using page level locking until the database if finally
exited by all users and the LDB file is deleted.

Isn't this the same as record level locking? I thought it was. Where can I
set this property? Also, I was just reading that using DAO to open
recordsets doesn't allow for "row level" locking (at least in A2000). I am
currently using DAO methods, of course.

And ensure that each user has a copy of the front end on his own
workstation, not a shared front end file on the network.

Yes, I intend to do this, but I must deploy on Tuesday, so unless by noon
tomorrow I can get the performance up I must go back to the unsplit database
and I will curse Access for any further problems. It just shouldn't be this
difficult.

Thanks for your help, Gunny.

 

[David Portwood]

Is the performance acceptable if you put the unsplit database on the

network drive?

Yes, still very fast! Evidently it is the act of splitting itself that
causes a huge performance degradation.

 

[David Portwood]

I'll try it without the persistent connection.

Hi, David,

to speed the performace up you could also try
http://support.microsoft.com/kb/150384/.

I have Czech version of Access and I've found out that persistent
connection, for an unknown reason, does NOT help.
See
http://groups.google.co.uk/group/mi...own+performance&rnum=1&hl=en#7b97839a87e6d970.

Vlado

 

[Dirk Goldgar]

In

Yes, still very fast! Evidently it is the act of splitting itself that
causes a huge performance degradation.

That is very strange, if you really do have a persistent connection to
the back-end. I suggest you verify this by opening the front-end and
then verifying that an .ldb file (corresponding to the back-end .mdb)
has been created in the network folder that holds the back-end. If so,
something is interfering beyond the normal issues of concern in a split
database.

If you put a text file in the backend's network folder, does it open
promptly when you double-click it?

 

[David W. Fenton]

Is your database file located in the root directory (or very close
to it) on the networked server

That is, the root directory of the *share* you are accessing it
through.

 

[David W. Fenton]

The standard path to My Documents is C:\Documents and
Settings\<User Name>\My Documents, so that's going to be a slow
path, because the operating system has to call the API function
for long file names to resolve the name on each of those
directories, in addition to calling the network API function to
check whether the user has permission to read each of the
directories in the path to the file.

I don't think you're right about resolving the permissions on each
folder above, as there's a setting for "traverse directory
permissions" (or something like that) that allows you to give access
to a file in a subdirectory to user groups that are prevented from
seeing files in the parent folder of that subdirectory.

I also don't think there's any problem with using *local* long
pathnames with spaces. It's on the server shares that it matters.

I don't worry so much about 8.3 as I do about just not using spaces
in any file names. I do it more to avoid having to use quotation
marks at command prompts. I also try to use the shortest possible
names, e.g., my application files are in D:\Programs, not C:\Program
Files.

I also wonder, based your comments above, where exactly you put your
front end files on workstations. You have to choose a place on your
local workstations where users have full permissions, and the only
place that this is the case with default Windows permissions (since
Win2K) is in the documents and settings folder. Any other location
requires either running as a local adminstrator (or power user) or
setting full control for another folder on each workstation.

I really think the concern over the My Documents location is
unwarranted.

 

[David W. Fenton]

Use the row level locking database option,
not page level locking.

Er, that would slow down performance, not speed it up.

Also, if the MDB was converted from pre-Jet 4, it can never use
row-level locking.