LDB Questions

[Unc]

We have a front end written in VBA and Access 97 to access
our customer data. We have approximately 25 PC's that can
at any point in time be logged into this database. I have
been using LDB Viewer for testing. The MDB sits on a
Netware 5.0 server and the PC's are all Windows 2000, SP2.

My questions are:

1) Through testing I discovered that once a connection is
made to the database and the LDB is updated to show that
connection then that connection never gets removed from
the LDB when the user closes the MDB. For example I can
see where PC#1 gets logged into the MDB and then when PC#1
exits the MDB the LDB shows Logged On = NO. Is this by
design?

2) Also, testing has shown that if a connection is showing
as Logged On = NO then the next PC that gets into the MDB
can use that connection in the LDB instead of creating a
new one. Is this what you see also?

3) The longer time goes by the more of these connections
in the LDB show up in LDB Viewer. Even though people are
getting in and out of the database the list of connections
only gets longer and longer. Is this correct or do I have
a bigger problem? Keeping in mind that I only have 25 PC's
on the network why would the LDB show 200+ connections
after 4 or 5 hours of use?

4) As a result of #3 above I can see with the results of a
packet capture that after the list of connections begins
to get longer and longer it takes more and more time just
to open up the MDB. The packet trace shows "Attempt Lock"
then "Fail" then another attempt, etc. One of the traces I
took showed 14 failed lock attempts before the MDB was
finally opened. Is this common and working by design?

Thanks for your help everyone!
Unc

 

[Albert D. Kallal]

We have a front end written in VBA and Access 97 to access
our customer data. We have approximately 25 PC's that can
at any point in time be logged into this database. I have
been using LDB Viewer for testing. The MDB sits on a
Netware 5.0 server and the PC's are all Windows 2000, SP2.

My questions are:

1) Through testing I discovered that once a connection is
made to the database and the LDB is updated to show that
connection then that connection never gets removed from
the LDB when the user closes the MDB. For example I can
see where PC#1 gets logged into the MDB and then when PC#1
exits the MDB the LDB shows Logged On = NO. Is this by
design?

Most likely this is by design. Remember, the ldb file is really just a
database file anyway. The fact that the ldb file re-cycles records, and does
NOT delete the record is probably a very good design. We all know that for
the last twenty years that most pc database systems DO NOT recover deleted
records. So, if you have some design that puts records into a list (in this
case users), then you will as a matter of good design not delete the record,
but only empty a few fields. So, when you next need a new record, you first
look for un-used records in the list. Only if you do NOT find a un-used
record, do you then add new records (this reduces file bloat, and any good
developer will do this as a matter of good design since WE KNOW that JET
does not recover un-used space until a compact). In fact, when I wrote my
own custom locking routines for ms-access, I did the exact same thing, and
don't delete records, but simply re-cycle them. So, no, not really surpassed
at all.

2) Also, testing has shown that if a connection is showing
as Logged On = NO then the next PC that gets into the MDB
can use that connection in the LDB instead of creating a
new one. Is this what you see also?

See my above explain. Sure, it makes sense. How else would one make this
work, given that JET does not recover deleted records. So, as mentioned, any
good design will avoid deleting records.

3) The longer time goes by the more of these connections
in the LDB show up in LDB Viewer.

Yes, but I think we should not call them connections, but just simply
records in a list. I mean, to really check live connections, you need to use
your network admin system, and look at who has the list of files open. I
mean, really, to confuse just a simply list of entry's in a data file with
actually network connections is stretching things a LOT!
That is just a list entries, not actual network connections. As mentioned,
fire up the network admin tools, and you can see who actually has live
connections to the file. A list of 200 entries in a small database file is
really nothing but a crumb in terms of data size..

Is this correct or do I have
a bigger problem?

As mentioned, a list of 200 entries is in the range of a page buffer size.
No, I can't imagine a list of 1, or list of 200 being of any significant
size. Ask your self have you ever noticed the difference in performance when
working on a table of 1 record, or a table of 200 records. (I never seen a
difference, and generally 1 disk read will bring in about 200 records
anyway).

Keeping in mind that I only have 25 PC's
on the network why would the LDB show 200+ connections
after 4 or 5 hours of use?

As mentioned, the file is simply a database. How many network connections
does the server show? As mentioned, any good developers as a matter of
course will re-cycle those records.

4) As a result of #3 above I can see with the results of a
packet capture that after the list of connections begins
to get longer and longer it takes more and more time just
to open up the MDB. The packet trace shows "Attempt Lock"
then "Fail" then another attempt, etc. One of the traces I
took showed 14 failed lock attempts before the MDB was
finally opened. Is this common and working by design?

Yes, I heard that the attempts to lock, and in fact I believe JET tries to
delete the ldb file first (I think quite a few times in fact. This "try to
delete" the whole ldb file has been fingered as a major performance issue).
On new win 2000 systems, this CAN CAUSE HUGE delays in open time.

The solution:
The generally accepted solution to this performance problem in this case is
to make sure that the front end application keeps a persistent connection.
That means the front application needs to "keep open" a table to the back
end. (it can be any old table). In fact, we get posts ON A DAILY BASES where
keeping a persistent connection solves performance problems when moving to a
win2000 network setup. For this trick, and other MUST READ performance tips,
check out:

While the above speaks of a2000, and not a97, the persistent connection
trick and information APPLIES EQUALLY WELL to a97.

Note that you can launch the front end application, but when a table is
opened, then the locking file comes into play. When you close the table,
then the locking file is released. So, keeping a table open in the front end
eliminates this rather large amount of overhead of trying to lock, and
un-lock the ldb file (we are talking at the network/netware level here..not
those entries in the ldb list).

You did not mention the kind of slow downs you are experiencing, but keeping
a table opened at all times to the back end can result in DRAMATIC
performance improvements.

 

[Cheryl Fischer]

Albert may have been using his "transparent font" at the end of his response
to your question #4 ...

I think that the Must-Read Performance Tips he mentioned are the ones found
at:

http://www.granite.ab.ca/access/performancefaq.htm

I believe you'll find them very helpful.

 

[Albert D. Kallal]

Thank you kindly....yes, I did leave out that link!

 

[Tony Toews]

1) Through testing I discovered that once a connection is
made to the database and the LDB is updated to show that
connection then that connection never gets removed from
the LDB when the user closes the MDB. For example I can
see where PC#1 gets logged into the MDB and then when PC#1
exits the MDB the LDB shows Logged On = NO. Is this by
design?

That's what I've been seeing too.

2) Also, testing has shown that if a connection is showing
as Logged On = NO then the next PC that gets into the MDB
can use that connection in the LDB instead of creating a
new one. Is this what you see also?

That's what I've been seeing too.

3) The longer time goes by the more of these connections
in the LDB show up in LDB Viewer. Even though people are
getting in and out of the database the list of connections
only gets longer and longer. Is this correct or do I have
a bigger problem? Keeping in mind that I only have 25 PC's
on the network why would the LDB show 200+ connections
after 4 or 5 hours of use?

Now this is *NOT* what I've been seeing. A97 on a Win NT4.0/2000
server has only ever had as many entries in the LDB file as the
maximum number of users that day. So I look at it and say, ah, 23
users were in the MDB at the peak today.

And I'd be somewhat concerned once you hit the 254th or 255th
connection slot.

I have seen this particular problem occasionally in the newsgroups.
The consistent factor was the Novell server. Now I know that some
configurations/versions of Novell client software, etc can lead to
corruption problems. You haven't yet mentioned corruptions but it's
possible that your version of Novell client software is a slightly
troublesome version.

4) As a result of #3 above I can see with the results of a
packet capture that after the list of connections begins
to get longer and longer it takes more and more time just
to open up the MDB. The packet trace shows "Attempt Lock"
then "Fail" then another attempt, etc. One of the traces I
took showed 14 failed lock attempts before the MDB was
finally opened. Is this common and working by design?

Now, as Albert and Cheryl points out, this behaviour sure sounds like
the LDB locking performance problem. So I'd certainly try that
solution to see if it helps with problem #3 & #4.

Albert, if I ever make a typo and key your name as Alberta do forgive
me in advance. To the lurkers I live in Alberta so I always
automatically add the eh (a) and have to backspace. <chuckle>

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