473,287 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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
Jun 27 '08 #1
6 4332
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
Jun 27 '08 #2
On Apr 16, 3:42*pm, Bruce <deluxeinformat...@gmail.comwrote:
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.
Jun 27 '08 #3
On Apr 17, 8:31 am, jsac...@comcast.net wrote:
On Apr 16, 3:42 pm, Bruce <deluxeinformat...@gmail.comwrote:
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
Jun 27 '08 #4
On Apr 17, 10:02*am, Bruce <deluxeinformat...@gmail.comwrote:
On Apr 17, 8:31 am, jsac...@comcast.net wrote:


On Apr 16, 3:42 pm, Bruce <deluxeinformat...@gmail.comwrote:
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- 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
Jun 27 '08 #5
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?
Jun 27 '08 #6
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
Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
8
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
15
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
3
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window...
4
by: pokerboy801 | last post by:
OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.