Access Networking

  • Thread starter Thread starter Guest
  • Start date Start date
A HUGE "Thank You" John for performing this test, and telling us how to reproduce it. I
intend to do just that later this week when time permits.

I asked this question tonight at the Seattle area Access SIG group meeting, held on the
first Tuesday of each month on the Microsoft campus in Redmond, WA. Michael Kaplan was
present, and he answered my question. I believe I am quoting him correctly as follows:

No index means Access must pull all of the data over the network
Index present means Access can pull only the required data

On 11/30, Albert Kallal stated "I don't know where this myth, or idea comes from." I
believe I can provide that answer later this week, when I find the time to transcribe,
word-for-word, information from more than just one published source. The authors do not
mention indexes in the context of their discussions. This issue seems to have quite a few
people confused--and, it appears for good reason, given the information that is available.

Tom
______________________________________________


Tom-

I responded to your "relational" assertion yesterday. After spending some
time running some performance tests this morning, I have some numbers that I
trust will convince you that Access (JET) fetches only the minimum it needs
over the network to get the job done. You can easily reproduce this test
yourself.

Database: 1.8 million rows, name/address information. MDB file size: 420
meg. No index on City.

Problem: Find all people who live in "Baytown" - approximately 37,000 of
the rows.

Methodology: Shut down all applications that might use the network. Open
Windows XP network status window on the LAN connection. Start Access, open
the database, and open a simple query in Design View: SELECT * FROM
tblPeople WHERE City = "BayTown"

Write down the "Bytes Received" in the network status window. Switch back
to Access, switch to Datasheet view in the query, and click the Go To Last
Record button on the navigation bar.

With no index, it took approximately 55 seconds to find the last row, and
the LAN sent 14 million bytes to my machine. Note that this isn't the
entire file even though JET clearly has to do a table scan to solve the
query. Because I displayed only the first and last "page" of the Datasheet
(a total of about 100 rows), Access is doing a full fetch on only the rows
it needs to display.

Next, define a Duplicates OK index on the City field. Close the database
and close Access to flush all buffers (you can reboot if you like). Restart
Access, open the database, and open the query in Design View. Note Bytes
Received in the network status window, switch back to Access, switch to
Datasheet view, and immediately click go to last.

Result: Less than 2 seconds and only 69,000 bytes transferred. Although
there are 37,000 matching rows, again JET is transferring only the blocks
needed to satisfy my display request of the first and last "pages" of the
datasheet.

Run the same test on SQL Server, and you get about 100,000 bytes transferred
in both cases, but the query without the index takes nearly as long to solve
as JET. The only difference is all the searching is being done on the
server.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Tom-

I fundamentally agree with Albert. Go look at my numbers again. The
database - with only ONE table in it compacted, is 420 meg. Granted, some
of that is overhead for other indexes that I have defined. When I remove
all indexes and compact the file, it's still 320 meg, but Access was able to
solve a "full table scan" by moving only 14 meg over the network. I don't
know the exact mechanics of how it does this, but JET clearly has a way to
ask the file system to return only pages (or pointers to pages) that contain
certain strings. I do know that JET doesn't actually fetch the entire page
until you navigate to it - it fetches and keeps on the local machine only a
list of pointers to the target pages. This also explains why, as I've
always said, that the first thing you can do to a machine to make Access run
faster on it is to give it more memory to work with.

Of course, when you give JET at least one index to work with, the amount of
data fetched drops dramatically because JET can use the index (sometimes
intersecting multiple indexes with "Rushmore") to pinpoint the exact pages
needed to solve the request. The "average" row in this table contains about
150 bytes. Access fetched the 37,000 row solution basically at the "blink
of an eye" and displayed the first and last set of records while fetching
only 69,000 bytes. If it were fetching all the data for all 37,000 rows, I
would expect it to fetch nearly 4 million bytes!

So, those who rumor that "JET is dead" are just plain wrong. It's not only
inexpensive and very relational-like; it's also drop-dead fast. Microsoft
would be silly to throw this technology away.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top