How does Access 'talk' to linked Access tables?

[Robert_L_Ross]

We recently moved our data center and with that move all of our databases
that have links to other .mdb tables have slowed to a crawl.

Our ITS group installed a network appliance called 'Riverbed' that is
supposed to only send 'changed' data on the wire. So the first time you open
a file, it's cached on both ends of the wire, so the first time opening a
file is slow by design. Afterwards however, it checks to see if the file has
changed on the back end, and if not it runs it from the 'local' box on your
end of the wire. It senses 'changes' by actual pattern recognition at the
bit/byte level. When it detects a change, it sends just that change over the
wire to the data center end and publishes that change.

Now, when we have a DB with ODBC connections to SQL tables, it runs
speedy...links to .mdb files are incredibly slow.

Our ITS group thinks that Access communicates using NetBIOS to communicate
to other .mdb files, but there's no configuration in .mdb linked files like
there is for ODBC connections, so we don't know.

We're wondering if the problem is with the communication slowdowns (either
..mdb to .mdb or even with the .ldb files - we have NO idea how the 'Riverbed'
appliance handles those) is there a way to use ODBC to link to a JetDB file
like Access? Trying to do it using the link wizard and choosing ODBC then a
..mdb back end says you can't use it, but we're wondering if there's a
work-around.

Thanks!

 

[Klatuu]

You are in a difficult situation.
Since this Riverbed product evaluates the data and only sends changed data,
it is going to be a problem when linking to mdb back end databases.

The issue is that Jet is a file server database engine, not a client server
database engine like SQL Server. With SQL Server, the data side processing
is on the server, so each time data is moved between an front end mdb and SQL
Server, it is different data. SQL Server evaluates the request for data and
only sends what was requested. Jet, on the otherhand, move the entire
recordset across the network, so if you have a fairly large recordset,
Riverbed has to evaluate the entire recordset continually. This is why you
are seeing such poor performance.

Unfortunatly, there is no work around. It may be you will have to upsize to
SQL Server.

 

[dch3]

Enlighten me...

Is it a correct statement that the instance of Jet running under the front
end reaches out to the tables in the back end, grabs the data, brings the
results back to the local machine? Hence a front end/back end running on a
local machine will typcially be faster than a front end/back end running on a
local machine/local server(same building) which will typically be faster than
a front end/back end running on a local machine/distant server.

 

[Klatuu]

That is correct. However, performance is more dictated by the configuration
of the network than by the physical location of the server.

The basic difference between Jet and SQL Server is that Jet runs on the
client machine where SQL Server runs on the Server machine.

In both cases, all data requests go from the front end application, whether
it me Access or some other application, to the database engine.

That means that the engine, then, is the entity that stores and retrieves
the data. since SQL Server is running on the server, the data is close at
hand and doesn't have to traverse the network for evaluation. With linked
Access tables, the application makes the request to Jet which is on the
client with the application. If the data is on a file server, Jet has to
pull all the data from all the tables referenced by the request across the
network to be evaluated before passing the requested data back to the
application.

Now, that doesn't mean it has to happen for every request. Once a recordset
is opened, Jet stores as much of the recordset is it can in memory and
establishes references to the data for faster retrieval. That is why you
will see developers use a MoveFirst MoveLast when they open a recordset.
That is to force Jet to load the entire recordset.

But still, in the final analysis, SQL Server is faster in most cases because
it causes less network traffic.

 

[John W. Vinson]

If the data is on a file server, Jet has to
pull all the data from all the tables referenced by the request across the
network to be evaluated before passing the requested data back to the
application.

Just lest this be misinterpreted... the JET engine does not (as some have
accused) bring the entire contents of the table down the wire for every query.
It uses any Indexes on the table to selectively bring down only those records
which match the indexed values.

 

[Guest]

Jet uses the Windows SMB - server message block - protocol.

That is, Jet uses the (old) database primitives of the (Windows)
operating system to communicate with the MDB file.

The Windows Network Redirector redirects this API across
the network using SMB, to the Windows SMB server, where
it is understood as the Windows database api.

Your Server has a file cache, so the mdb is probably in
memory rather than on disk, and your Workstation has
a network cache, so the mdb may be in your network cache
rather than on the server.

When only one user is using the MDB, the server automatically
locks the MDB for single user, which allows the network
redirector to locally cache the entire file. It sounds like
'Riverbed' is supposed to be an enhanced version of this.

However, when the file is shared and actually open by multiple
users, you can't each use your own copy of the file. It may be
that 'Riverbed' can't handle shared access.

As far as I know, SMB runs over NetBIOS over TCP over
IP.. SMB is also used for Group Policy Objects and Roaming
Profile. For this reason, most SMB servers now have TCP
Packet Signing enabled. Packet Signing makes SMB transactions
much slower, and is specifically designed to defeat man-in-the-
middle appliances like 'Riverbed'. I wonder if 'Riverbed' actually
caches at all when signed packets are used?

There is also an Access/Jet cache, but that can't help you,
because the delay is actually as Jet waits for record locks.
It may be that 'Riverbed' can't handle record locking: it may
be built on the assumption that only file locks are used.

To the network system, LDB files are just small database
files. The data is just connection information to allow the
system to report which user and pc has which record locked
in the main database. This system is used because it is
very old. Again, these are shared files, with record locking.
The only other problem with LDB files is that to avoid leaving
them behind, they are deleted by the last connected user,
and created by the first connected user. How well does
'Riverbed' handle deletion and creation? This can be a very
slow process.

By the way, are your MDB files on the SQL Server? Network
delays like DNS problems can also make a big difference.

And Anti-virus programs can cause delays as well.

---ODBC---
An ODBC driver is a program that responds to ODBC requests.
There are several SQL Server ODBC drivers that understand
network protocols like "named pipes" or "tcpip" and convert
ODBC into private communications with a SQL Server. As it
happens, those private communications may look like ODBC
communications (ODBC was a MS idea), or not (MS has some
more recent protocols). SQL Server has a TCPIP port that
responds to ODBC text: you can telnet to it and type in select
statements. This is why it was easy for MS to do SQL Server
ODBC, but it is not ODBC: ODBC is the Windows Driver
Specification, which specifies that ODBC drivers will fit into
the ODBC driver framework and respond to ODBC SQL.

The Access ODBC driver understands ODBC. It emits
Windows Database API. It has no network libraries. There
is no other Access ODBC driver.

There is no Access "ODBC server". If you run Access on your
server it does not open a port and listen for ODBC SQL.

You can use a SQL Server ODBC driver to connect to SQL
Server, and have the SQL Server connect locally to an
MDB. (Not just SQL Server. There are other driver/server
programs that can connect to and publish data).

---Additional Notes---

Generally SQL Server ODBC is slower than Access/Jet.

SQL Server scales better to more users, and to larger databases.

JET is generally unusable across a WAN.

Although ODBC performance across a WAN is generally
unacceptable for end users, it is not as bad as JET.

---

To get acceptable performance out of SQL Server, it is generally
necessary to design data access to minimise the number of
records returned. This is partly because SQL Server ODBC will
store as much of the recordset is it can in local memory (Assuming
that you really wanted everything you asked for). Jet generally
gets fewer records, because it assumes a wide channel to the
data will allow it to get data on demand.

However, Jet requires fast low latency channels because all of
the database locking is done across the channel. The client
locks the records on the server. You have to wait until the lock
is confirmed before you can request the record. You have to
wait to lock the index before waiting to lock the record. If the
channel is slow, you have to wait. If packet signing is implemented,
you have to wait for a reply to each TCPIP packet before
sending the next one.

In contrast, SQL Server handles all locking at the server.
It decides at the server which individual records and tables to lock.
Although more data may be returned, the conversation is shorter.

 

[Klatuu]

Thanks for the clarification, John.