Memory usage on large queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tables that range to about 500k rows and use them in various joined
queries. I know access runs these queries on the client, but what I was
wondering about was how much data has to be brought down from the server to
the client. I assume it is query dependent, but what are the rules. This is
most critical for us, since we are running on Citrix and our memory is highly
utilized.
 
Here is about the worst news you could ever have wanted to hear:

Access brings down ALL of the data to the client, then throws out what it
DOESN'T need at the client level, then renders it to the form/report/query.

Don't shoot the messenger, hmmmmmkaaaaay
 
I'm not so sure that is true. I have heard it stated that Access might
bring down only the indexes if you are filtering records using the indexes
and then brings down the matching records and fields after that.

I don't know. I don't have a sniffer to check all the packets and I don't
have any inside information from Microsoft on exactly how Access handles
queries. I do know that Access + Jet is a file server application and not a
database server, so the processing is done on the "local" machine.
 
I can break out the Jet Guide and verify.

John Spencer said:
I'm not so sure that is true. I have heard it stated that Access might
bring down only the indexes if you are filtering records using the indexes
and then brings down the matching records and fields after that.

I don't know. I don't have a sniffer to check all the packets and I don't
have any inside information from Microsoft on exactly how Access handles
queries. I do know that Access + Jet is a file server application and not
a database server, so the processing is done on the "local" machine.
Click to expand...
 
John,

I spoke to the Author of the Jet Bible(The Microsoft Jet Database Engine
Programmer's guide) and here are his comments:

"Detach your hard drive from your computer. Walk it down the hall, and
connect it with a wire. That is how Access treats ALL queries."

What he's saying is that Jet treats every data source like a hard drive.
So, whether it sits in your local box(a short wire), is down the hall(a
longer wire), or is across a WAN(a really long wire), Jet behaves the same.
For each query, Jet must toil and tinker with the data across the wire,
until it gathers all data needed for the query. (Even if it passed back the
indexes first, it must still toil and tinker across the wire.)

So, I think we're both kinda wrong. But, it is important for the OP to know
that Jet does NOT behave like SQL Server, thus performance is going to be a
serious issue.
 
I guess I was unclear. I understand what you are saying and that was my
understanding also. I just meant to point out that Access + Jet doesn't
necessarily bring the entire table across "the wire" to generate query
results.

Thanks for the additional feedback.

[MVP] S.Clark said:
John,

I spoke to the Author of the Jet Bible(The Microsoft Jet Database Engine
Programmer's guide) and here are his comments:

"Detach your hard drive from your computer. Walk it down the hall, and
connect it with a wire. That is how Access treats ALL queries."

What he's saying is that Jet treats every data source like a hard drive.
So, whether it sits in your local box(a short wire), is down the hall(a
longer wire), or is across a WAN(a really long wire), Jet behaves the
same. For each query, Jet must toil and tinker with the data across the
wire, until it gathers all data needed for the query. (Even if it passed
back the indexes first, it must still toil and tinker across the wire.)

So, I think we're both kinda wrong. But, it is important for the OP to
know that Jet does NOT behave like SQL Server, thus performance is going
to be a serious issue.
Click to expand...
 
Back
Top