[AccessD] Access and SQL Server - Where is the work done - How ManyRows are Shipped Across the Network

<<I would guess that the "heavy lifting" is being done by the SQL Server
database box and only 100 rows are shipped back to the Access application on
the PC.>>

 Pretty much.

<<Is this always the case, or is it possible that all of the 1,000,000 rows
are sent back to the Access application depending on the complexity of the
SQL?>>

 Depends on the query.  JET tries it's best to send the entire ODBC query to
the data source, but if you've included JET specific expressions or joins to
local tables, then JET requests all rows.  If your in doubt, you can turn on
ODBC tracing and see what requests are being made by JET.   But on a table
that large, it's pretty obvious if it's grabbing all the rows.

 If you use a pass-through query, JET never touches it and the SQL syntax
must be written for the ODBC source.

 In cases where the table in JET is an SQL View, the preparation of the view
is all done on the SQL side of course.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, March 12, 2013 12:12 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access and SQL Server - Where is the work done - How
ManyRows are Shipped Across the Network

All,

This is just a curiosity question.

Let's say that I have an Access application that obtains data from a SQL
Server database via ODBC.

There are 1,000,000 rows in one of the SQL Server tables.

There is a query in the Access application that returns 100 rows from this
table based on the "Where" condition in the query.

I would guess that the "heavy lifting" is being done by the SQL Server
database box and only 100 rows are shipped back to the Access application on
the PC.

Is this correct?

Is this always the case, or is it possible that all of the 1,000,000 rows
are sent back to the Access application depending on the complexity of the
SQL?

Again, these are just curiosity questions.

Thanks,
Brad