White Paper on How MSAccess interacts with SQL Server Back End?

V

Vihrea

Hi All,

I'm having some performance issues with someoe trying to retrieve data from
my SQL 2000 db using ACC2003 as the front end via linked tables.

I'd like to know more at a technical level about how ACC compiles an data
access path, hands it off os SQL2K , how SQL interprets it and then returns
the dataset.

Finally, exactly what is ACC2003 then processing? Is it the entire dataset
(>6million rows). Perhaps this user needs to redesign the query. I don;t
know enough about ACC's behavior yet.

Any references out there?


======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNA, .NET
 
S

Sylvain Lafontaine

First, this newsgroup is about ADP and has nothing to do with ODBC linked
tables. While some of the people hanging around here will have a more or
less depth knowledge of ODBC linked tables, you can decrease the luck factor
by posting to a newsgroup more dedicated to that specific subject such as
m.p.a.odbcclientsvr or mpa.externaldata; where m.p.a. and mpa. are
abreviations for microsoft.public.access.

As to your question, personnally, I never heard of any white paper or of a
technical article or book specifically dedicated to that subject; however,
you can find here and there some anecdotal pieces of information that will
say:

For simple queries, Access will create a TSQL queries that will run on the
server and retrieve the primary keys of the desired result and will use
these keys to retrieve the other fields from the tables by group of 10 rows.
For more complexe queries, for example queries with complexe LIKE statements
or with VBA functions that Access cannot translate directly into T-SQL
functions; Access will retrieve all the rows and then make its own
filtering. Of course, in this situation, the performance will rapidly sunk.

You can easily take a look at what Access is doing by using the SQL-Server
Profiler; which will show you exactly what Access is doing in every
situation.
 
V

Vihrea

I'll try another group, Sylvain.

Thank you for your response
--
======================
JG
Systems/DBA Supervisor
Vermont Agency of Transportation
GIAC Security Certified
MCSE, CCNA
 
A

a a r o n . k e m p f

the answer is that Jet scans the whole table across the network and
then does the math locally.

you _SHOULD_ be using ADP for anything that you do, because it sounds
to me like you know how to write TSQL

thanks

-Aaron Kempf
MCITP: DBA SQL 2005
 
Top