Re: Problem using Access or Query Designer to run queries in SQL S

On Mon, 16 May 2005 18:26:03 -0700, "BI_Specialist"
<BISpecialist@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>1. I AM trying to fetch less data via use of where clauses, when I query
>against a SQL server based table (linked via ODBC). And that is exactly when
>I get the timeout error. Didnt' matter if it was a Select Query or a
>Pass-thru query, still got the ODBC timeout, even though I was pulling in 4-5
>fields only and maybe 250 rows after grouping.

The idea is to perform all data processing on the server, not the
client. Sometimes a Profiler trace can help you troubleshoot. Another
option is to code complex queries as stored procedures, which
guarantees that all processing is done on the server. If you are using
Access queries, then you have to be aware that using functions or
expressions in queries can cause processing to bog down since the
expression service must then process each row individually.

Using Access queries against linked ODBC tables can work, but you have
to be careful in coding them.

>2. I can understand if my query times out when trying to retrieve ALL 5
>million rows from SQL server..but noooo...that works fine!! Is this is a
>"limitation" of Access? Don't understand how a Query-form interface would
>help or be any different than a pass-through query, if the SQL being sent to
>the server is the same.

As in my answer to 1), it depends on what is being processed where.
Without seeing actual query syntax, it's hard to tell. Take a look at
a Profiler trace to analyze the SQL. Even if the SQL sent to the
server is the same, it may not be processed the same way.

Let's clarify what is and what isn't a limitation of Access. When you
do a SELECT * FROM in an Access query, you're not really fetching all
million rows. You're only fetching the first few rows and the key
values to the remaining rows, in what is known in Access as a dynaset,
or keyset cursor. As you scroll through the UI, the data in the
remaining columns is fetched. This creates a situation where locks are
held on the data and the network is continuously in use fetching rows.
You don't get all million rows in a gulp, even though you may think
so. The real limitation here is that Access is tying up network and
server resources while holding on to that keyset cursor. This design
pattern works OK when it's an Access-Jet app, but is terrible for an
Access-SQL app.

>But if someone could please tell me where the timeout is set within
>Enterprise Manager's Query Designer, I'd be very grateful. The symptoms are
>identical to Access (can view all records, but can't apply any conditions),
>but not sure what the connection type is and how to configure it. I guess
>everyone uses Query Analyzer, so this has never been an issue.

Tools|Options|Connections|Query Timeout. QA is not meant to be an
end-user tool for querying data, which is why it's not an issue.

Bottom line: appearances can be deceptive. If you want to build a
light-weight querying application in Access going against SQL Server
data that can handle more than a handful of users without bogging down
the network or the server, then you need to understand that Access
DOES NOT work out of the box, nor was it ever intended to. You'll
solve most of your problems if you learn how to code it the right way
using stored procedures, which you can call from pass-through queries.
Just stay away from cursors :-)

--Mary
.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • 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: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
Loading