Where is the Query Processed?

[Matthew]

Our company has a Windows-Based program designed in Access
2000 using Visual Basic. The program goes out on the LAN
and accesses an Access database. We are in the process of
converting over to SQL Server 2000.

If I send an "ad hoc" SQL statement from the Access
program to the SQL Server (which now has the new
database)...where is the query processed? Does the SQL
Server send my computer a page at a time and make my
computer crunch the numbers...or is it all processed on
the server and only the results of the query returned?

Along with you answer, can you provide some sort of
documentation if possible...I have to prove this to the
boss.

Thanks

 

[Lynn Trapp]

If you do a regular Access query, then the Jet engine does the processing.
If you use a Pass Through Query, then SQL Server does the processing and
returns the results. You can search for information under Pass Through
Queries in the Access Help.

 

[Albert D. Kallal]

The query is generally processed on the server.

However, if your stored querydef (on the access side) uses multiple tables,
then much processing can occur on the ms-access side. So, in that case, you
move the query to the sql server side and create what is called a view. this
will force the joins and processing to occur on the sql side.

Also, using ADO to connect to the server will force the processing to occur
server side.

However, linked tables in ms-access to sql server generally work fine
(except as mentioned when multiple tables etc are involved). So, some
tweaking of code and queries here and there is needed to properly take
advantage of sql server.

Often, if the access/JET application is well written, then no increase in
speed will be observed when moving to sql server. With a good designed
application, JET/ms-access does a good job, and the amount of records that
is pulled across a LAN can be quite small, even with large tables. Simply
moving to a server based system does not always yield performance
improvements unless designs are changed to take advantage of the server.