Access Query on SQL Linked Tables

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

Re: Access Query on SQL Linked Tables

On Apr 16, 9:49 am, jsac...@comcast.net 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

Re: Access Query on SQL Linked Tables

On Apr 16, 3:42=A0pm, Bruce wrote:
> On Apr 16, 9:49 am, jsac...@comcast.net 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. =A0The join field in both tables are indexed and I'm
> > selecting 1 field from each table to lookup. =A0The 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. =A0I 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? =A0If 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? =A0If not, it sounds like it probably
> should be. =A0If you are not familiar with pass-through queries, look
> them up in help. =A0They basically pass the query processing to SQL
> server rather than letting Jet handle them locally. =A0It's a fairly
> simple matter (for simple queries) to convert to pass-through. =A0In 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.

Re: Access Query on SQL Linked Tables

On Apr 17, 8:31 am, jsac...@comcast.net wrote:
> On Apr 16, 3:42 pm, Bruce wrote:
>
>
>
> > On Apr 16, 9:49 am, jsac...@comcast.net 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

Re: Access Query on SQL Linked Tables

On Apr 17, 10:02=A0am, Bruce wrote:
> On Apr 17, 8:31 am, jsac...@comcast.net wrote:
>
>
>
>
>
> > On Apr 16, 3:42 pm, Bruce wrote:
>
> > > On Apr 16, 9:49 am, jsac...@comcast.net 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. =A0The join field in both tables are indexed and I'm
> > > > selecting 1 field from each table to lookup. =A0The 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. =A0I must confess that I'm new to the Profiler, so I m=
ight
> > > > 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? =A0If so, is there a w=
ay
> > > > for me to see this activitiy within the SQL Profiler?
>
> > > > Thanks for any insight you can provide,
> > > > Jim
>
> > > Is this a pass-through query? =A0If not, it sounds like it probably
> > > should be. =A0If you are not familiar with pass-through queries, look
> > > them up in help. =A0They basically pass the query processing to SQL
> > > server rather than letting Jet handle them locally. =A0It's a fairly
> > > simple matter (for simple queries) to convert to pass-through. =A0In S=
QL
> > > 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. =A0I've pretty much cured the problem by packaging
> > the SQL in a stored procedure with parameters and calling it with a
> > passthru call now. =A0This is an old Access 97 app that has been in
> > place for some time now. =A0The 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. =A0Found 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. =A0Without being a passthrough query, Access'
> Jet database engine is handling the join and the processing necessary
> to return records in your result set. =A0While 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. =A0It 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. =A0I 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

Re: Access Query on SQL Linked Tables

jsacrey@comcast.net wrote in news:632ae324-05f3-4962-b17d-
aff64e40c01a@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?

Re: Access Query on SQL Linked Tables

jsacrey@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