Access with Linked SQL Tables

[Franco]

I was reading the article How to Migrate from Access to SQL Server 200
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx
The technical paper states.
Instead of retrieving the whole set of records using the form filter behind
the forms to get the desired result set, you should change the recordsource
property of the form to include a WHERE clause, so that the filtering will
occur on the server and the filtered set of results will be returned from SQL
Server:

The paper also states.
When a linked table is queried (through an Access query or otherwise), every
record in that table is returned from SQL Server, irrespective of any
filtering through the SQL WHERE, UNION, or JOIN clauses.

I’m somewhat confused. Does the WHERE clause actually filter the data on
the server or does it fetch all the data from the server and apply the filter
locally?

Thanks

 

[Albert D. Kallal]

I was reading the article How to Migrate from Access to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx

The technical paper states.
Instead of retrieving the whole set of records using the form filter
behind
the forms to get the desired result set, you should change the
recordsource
property of the form to include a WHERE clause, so that the filtering will
occur on the server and the filtered set of results will be returned from
SQL
Server:

Not only should you do the above for sql applications, but also for jet
based applications.

The paper also states.
When a linked table is queried (through an Access query or otherwise),
every
record in that table is returned from SQL Server, irrespective of any
filtering through the SQL WHERE, UNION, or JOIN clauses.

**** READ **** the paragraph heading:

**** Using Views for Queries with Complex Joins ****
The above text you are quoting is talking about complex joins. Without
question, jet will pull the whole table when you do complex joins. So, the
SIMPLY solution stated in that paragraph is to simply create a view on the
sql server side (another possible solution is to use pass-through query).

So, to be clear:

ms-access when used with a jet back end (mdb file). DOES NOT PULL THE WHOLE
TABLE over the network

ms-success when used with a sql server back end (sq server) DOES NOT PULL
THE WHOLE TABLE over the network.

When you do COMPLEX JOINS, jet will often screw up the query..and does pull
both of the tables over the network. So, as the paragraph above title
stats -- "use views" for queries with complex joins"

Yes, is *very* good advice, and it is correct.

However, when not doing complex joins, linked tables *can* be used without a
view, and often ms-access will not pull the whole tables. However, you are
VERY much more safe to use a view, and link to that view (they perform
*VERY* well,a nd also work well with "where" clauses when opening a form.

 

[Albert D. Kallal]

To further clarify:

However, when not doing complex joins

In the above, I don't mean not doing joins at all, I mean a simple normal
joins between two tables.

however, since JET linked tables to sql server *often* gets messed up, if
you experience a performance problem with joined linked tables, then I
suggest you use a view.

Joins on linked tables often don't perform well. (and, with complex
joins...it even worse..and tables will be pulled over the wire).

I also find the same occurs for combo boxes on forms. If you go:


select id, Firstname, Lastname, company from tblCustomers.


If the above is the *direct* setting for the combo box, the above sql *then*
has to look at the linked table....get the legal fields....check the table
link...and *then* pull the 4 columns of data.

*anytime* you resolve *any* local sql that has to resolve to a *linked*
table which in tern resolves to sql server...you find a delay occurs. You
can improve the response time by replacing the above combo box with a view
that retunes ONLY those above 4 columns (and, has your correct sorting
etc.). Then, create a link to that view, and then use the view name for the
combo box source

note:

O N L Y use the view name, DO NOT use ANY sql at all!!

eg:

nameOfView


If you use nothing more for the combo box then *just* the linked view name,
you find it runs better then using sql that resolves to a link table. You
will also find things snappy if you use just the table name..but, then you
can't control what columns are returned.

This trick will increase your load time by a lot on forms with several combo
boxes. However, if you have more then 4, or 5 combo boxes on the form, then
you often will notice some delays in the form load. (if you have a lot of
combo boxes, then hopefully some can be based on local tables...especially
if they are static. This is not a huge deal, but if you have many combo
boxes on a form, using views and removes the source sql for the combo boxes
will increase load times by a lot (in fact, it speeds things up enough to
avoid you having to write more code, and using more drastic measures to
speed up form loading time).

 

[Franco]

Thank you for the excellent response! This definately clarified the issue
for me.

 

[David W. Fenton]

Without
question, jet will pull the whole table when you do complex joins.
So, the SIMPLY solution stated in that paragraph is to simply
create a view on the sql server side (another possible solution is
to use pass-through query).

This is simply not true, Albert. Jet may or may not pull the entire
table -- it depends entirely on the indexing.

What I have found is that Jet executes SQL Server inner joins very
efficiently (i.e., it passes them off to the server), but outer
joins are *very* slow, even when you create a view on the server.
Because of this, I recommend avoiding outer joins whenever you can,
as there is really no way to optimize them.