Access Query on SQL Linked Tables

[jsacrey]

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

[Bruce]

On Apr 16, 9:49 am, [e-mail] wrote:

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

Is this a pass-through query? If not, it sounds like it probably
should be. If you are not familiar with pass-through queries, look
them up in help. They basically pass the query processing to SQL
server rather than letting Jet handle them locally. It's a fairly
simple matter (for simple queries) to convert to pass-through. In SQL
view, select Query, SQL Specific, then Pass-through on the menu.

Bruce

[jsacrey]

On Apr 16, 3:42*pm, Bruce <[e-mail]:

On Apr 16, 9:49 am, [e-mail] wrote:

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. *The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. *The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. *I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? *If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

Is this a pass-through query? *If not, it sounds like it probably
should be. *If you are not familiar with pass-through queries, look
them up in help. *They basically pass the query processing to SQL
server rather than letting Jet handle them locally. *It's a fairly
simple matter (for simple queries) to convert to pass-through. *In SQL
view, select Query, SQL Specific, then Pass-through on the menu.

Bruce- Hide quoted text -

- Show quoted text -

No, it's not a PassThru....it was a query that a user designed with a
simple join in it. I've pretty much cured the problem by packaging
the SQL in a stored procedure with parameters and calling it with a
passthru call now. This is an old Access 97 app that has been in
place for some time now. The tables that this app is looking at have
grown over the years and we're starting to see degradation in
performance with some of the Jet queries on linked tables.

I have a workaround, but would like to know if Access is dragging all
of those records across the network just to do the join in design
mode. Found out yesterday after posting my question that it takes
forever to just open the query in design mode also.

[Bruce]

On Apr 17, 8:31 am, [e-mail] wrote:

On Apr 16, 3:42 pm, Bruce <[e-mail]:

On Apr 16, 9:49 am, [e-mail] wrote:

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

Is this a pass-through query? If not, it sounds like it probably
should be. If you are not familiar with pass-through queries, look
them up in help. They basically pass the query processing to SQL
server rather than letting Jet handle them locally. It's a fairly
simple matter (for simple queries) to convert to pass-through. In SQL
view, select Query, SQL Specific, then Pass-through on the menu.

Bruce- Hide quoted text -

- Show quoted text -

No, it's not a PassThru....it was a query that a user designed with a
simple join in it. I've pretty much cured the problem by packaging
the SQL in a stored procedure with parameters and calling it with a
passthru call now. This is an old Access 97 app that has been in
place for some time now. The tables that this app is looking at have
grown over the years and we're starting to see degradation in
performance with some of the Jet queries on linked tables.

I have a workaround, but would like to know if Access is dragging all
of those records across the network just to do the join in design
mode. Found out yesterday after posting my question that it takes
forever to just open the query in design mode also.

It's hard to say whether Access is dragging _all_ of the relevent
records over the wire to produce your query results but it is safe to
say that it is dragging far more stuff over the wire than a
passthrough query will. Without being a passthrough query, Access'
Jet database engine is handling the join and the processing necessary
to return records in your result set. While Jet can certainly
optimize this to some degree based on its knowledge of the indexes and
predefined relationships between the tables it is not as efficient as
letting SQL Server handle all of that processing and simply hand over
the completed result set to Access, which is what a passthrough query
does. It sounds like your original query was a parameterized query
which SQL Server will only handle in the form of a stored procedure,
i.e., you cannot turn a query with replaceable parameters directly
into a passthrough query. I don't know if establishing relationships
and referential integrity rules between the ODBC tables at the Access
level would help (or is even possible) but if so that is something
that might help speed things up for the non-passthrough queries.
Sounds to me like you did just the right thing though, rewriting as a
sproc with parameters.

Bruce

[jsacrey]

On Apr 17, 10:02*am, Bruce <[e-mail]:

On Apr 17, 8:31 am, [e-mail] wrote:

On Apr 16, 3:42 pm, Bruce <[e-mail]:

On Apr 16, 9:49 am, [e-mail] wrote:

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. *The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. *The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. *I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? *If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

Is this a pass-through query? *If not, it sounds like it probably
should be. *If you are not familiar with pass-through queries, look
them up in help. *They basically pass the query processing to SQL
server rather than letting Jet handle them locally. *It's a fairly
simple matter (for simple queries) to convert to pass-through. *In SQL
view, select Query, SQL Specific, then Pass-through on the menu.

Bruce- Hide quoted text -

- Show quoted text -

No, it's not a PassThru....it was a query that a user designed with a
simple join in it. *I've pretty much cured the problem by packaging
the SQL in a stored procedure with parameters and calling it with a
passthru call now. *This is an old Access 97 app that has been in
place for some time now. *The tables that this app is looking at have
grown over the years and we're starting to see degradation in
performance with some of the Jet queries on linked tables.

I have a workaround, but would like to know if Access is dragging all
of those records across the network just to do the join in design
mode. *Found out yesterday after posting my question that it takes
forever to just open the query in design mode also.

It's hard to say whether Access is dragging _all_ of the relevent
records over the wire to produce your query results but it is safe to
say that it is dragging far more stuff over the wire than a
passthrough query will. *Without being a passthrough query, Access'
Jet database engine is handling the join and the processing necessary
to return records in your result set. *While Jet can certainly
optimize this to some degree based on its knowledge of the indexes and
predefined relationships between the tables it is not as efficient as
letting SQL Server handle all of that processing and simply hand over
the completed result set to Access, which is what a passthrough query
does. *It sounds like your original query was a parameterized query
which SQL Server will only handle in the form of a stored procedure,
i.e., you cannot turn a query with replaceable parameters directly
into a passthrough query. *I don't know if establishing relationships
and referential integrity rules between the ODBC tables at the Access
level would help (or is even possible) but if so that is something
that might help speed things up for the non-passthrough queries.
Sounds to me like you did just the right thing though, rewriting as a
sproc with parameters.

Bruce- Hide quoted text -

- Show quoted text -

Thanks for the insight Bruce! I know that the thing to do would be to
rewrite the whole app with an ADP and sprocs everywhere, but I'm a one-
man shop and this is one of our production systems for auditing
electronic records. Like everybody else, I have so much on my plate
at this point, it's hard to do that much coding with everything else
going on.

Take it easy,
Jim

[lyle fairfield]

js*****@comcast.net wrote in news:632ae324-05f3-4962-b17d-
af**********@m44g2000hsc.googlegroups.com:

Hey everybody, got a secnario for ya that I need a bit of help with.

Access 97 using linked tables from an SQL Server 2000 machine.

I've created a simple query using two tables joined by one field
between them. The join field in both tables are indexed and I'm
selecting 1 field from each table to lookup. The Access query is
taking more than 60 second to retrieve 1 record and if I execute the
same query within the Query Analyzer, it takes less than a second to
execute.

I've tried using the SQL Profiler to see what's being sent to the
server, but I don't see anything showing up there when I execute the
Access query. I must confess that I'm new to the Profiler, so I might
not be looking in the correct place for the SQL that is supposedly
being processed.

Do you think that Jet is retrieving all of the records from the SQL
machine so that it can run the query locally? If so, is there a way
for me to see this activitiy within the SQL Profiler?

Thanks for any insight you can provide,
Jim

What are you using the query for?

Can you post the SQL string?

[Rick Brandt]

js*****@comcast.net wrote:

No, it's not a PassThru....it was a query that a user designed with a
simple join in it. I've pretty much cured the problem by packaging
the SQL in a stored procedure with parameters and calling it with a
passthru call now. This is an old Access 97 app that has been in
place for some time now. The tables that this app is looking at have
grown over the years and we're starting to see degradation in
performance with some of the Jet queries on linked tables.

I have a workaround, but would like to know if Access is dragging all
of those records across the network just to do the join in design
mode. Found out yesterday after posting my question that it takes
forever to just open the query in design mode also.

You could use SQL tracing tools to see what exactly is being passed to the
server. Here is an example if what I have seen using SQL Trace when joining
two ODBC linked tables in Access.

Table1:
Field foo
Field bar

Table2:
Field bar

Access query joining on bar with a WHERE clause on foo.

Access pulls all desired fields from Table1 for rows where foo satisfies the
WHERE clause and then sends SQL to server for Table2 for rows where bar is
equal to...

first value of bar from Table1
OR next value of bar from Table1
OR next value of bar from Table1
etc...

So the query still has quite a bit of processing done by the server, but the
actual JOIN is not performed by the server. Rather it is replaced with a
whole bunch of OR criteria to match up Table2 bar to the values in Table1
bar.

Examining this you can see that if the number of rows in Table1 that
satisify the WHERE clause is small then the query can execute pretty
quickly. One would very much expect performance to fall off though as the
number of rows returned from Table1 increases.

So as I examine "standard Access queries" that use ODBC linked tables to see
which ones ought to be changed into Passthroughs or Stored Procedures I
first look at those that already perform slower than I would like. The next
group to look at then are those that have joins between one or more of the
linked tables.

And there is no speed advantage to moving everything into an Access project.
You can certainly go that route, but a Passthrough or Stored Procedure
executed via a Passthrough will perform just as well.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com