Re: Efficiency of Nested queries

From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 09:58:01 -0700

Allen, Thank you for that link. I found that article
very interesting and informative.

-Ted Allen
>-----Original Message-----
>The JOIN will probably be the most efficient way to
achieve this.
>
>Access uses a remarkably intelligent set of algorithms
to optimise your
>queries, but it does not really expose these to you.
There are some
>undocumented ways to see what's going on.
See "Micorosft's Unsupported JET
>Optimization Tools" at:
>http://msdn.microsoft.com/library/en-
us/dnacbk02/html/odc_4009c15.asp?
frame=true#odc_4009c15_topic4
>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia.
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>
>"scimitar" <scimitar@discussions.microsoft.com> wrote in
message
>news:254BFB9B-C65C-43E4-8D2C-
B90D7BF34E53@microsoft.com...
>> I'm struggling to make a complex query as fast as
possible.
>> If I have nested queries does the nested query run in
full before the
>query that reads it runs or does some optimization take
place?. For example:
>>
>> Lets say I have a table of 50,000 rows of which 100
records are 'open'.
>> I need to display the open rows but only where the
user = 'XYZ'. To
>determine the user requires a join with another table.
>>
>> Is it more efficient to have 2 queries where the first
query selects just
>the open records and the second query does the join
(thus the join only
>applying to 100 records), or is it more efficient to do
it with one query.
>
>
>.
>

Relevant Pages

  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • 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: "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: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)