Re: Access Query on SQL Linked Tables

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

Relevant Pages

  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... if your server doesn't have much memory ... and the "executing query" ball just kept ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... SQL Server MVP ... This is a dev server so I hadn't even been ... When I get properties for the FT catalog, ... I just ran a similar query on ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Single-Threading / Performance issues
    ... SQL Server MVP ... > Early this year my database was migrated from a single CPU, ... (when a long select query is running, ... server guy also sez it's not his problem. ...
    (microsoft.public.sqlserver.server)