Re: query efficiency

In general, the fewer phrases in the WHERE clause, the faster the query will
execute. The minimalist approach is best.

Of course there are many other factors that also affect the execution speed,
such as indexes.

If you are interested in trying to tune your queries, this link might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp


--
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.

"JB" <JB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:711EBBE9-4877-4368-9EB1-4FCFC0214EEE@xxxxxxxxxxxxxxxx
> This is a general question that I have wondered about from time to time
> but
> have never seen discussed in the books I have read. I will give an
> example
> to illustrate the idea
>
> SELECT * from tblEveryOneIntheWorld
> WHERE Grandfather is true and Age > 20 and Sex = male
>
> The last two contraints in the Where clause are clearly unnecessary when
> looking for grandfathers but they could be used to reduce the set of
> records
> to search for grandfathers. My question is the general one of whether it
> is
> better to tell the query everything one knows or to leave it at the
> minimal
> set of criteria that will solve the query? In this case, the test for
> grandfathers is just a boolean search but this type of question comes up
> from
> time to time when the cost of testing for the necessary part of the
> "where"
> is not minimal, eg involving joins, etc
> --
> JB


.

Relevant Pages

  • Re: Runtime error 2001
    ... the Where clause so that I could see as it was ... I can get the query to run with these criterion ... however I cannot seem to write the query via SQL and get it to run. ... you can execute that exact same SQL successfully then it's something else. ...
    (microsoft.public.access.modulesdaovba)
  • RE: selecting a value based on a date
    ... I still get the message "you tried to execute a query that does not include ... SO i tried replacing the having clause by the WHERE clause in the Waitdays ... chart pertaining to that dive was received. ...
    (microsoft.public.access.queries)
  • Re: counting records in select query
    ... You can execute a SELECT COUNTwith the appropriate WHERE clause to ... This means it will cost nearly twice as much as executing the query ... For simple queries counting is fine--but it just ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Query occasionally returning empty results in 8i
    ... clause, second a query with where clause (executed ... Misses in library cache during parse: ... Misses in library cache during execute: ...
    (comp.databases.oracle.server)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)