query efficiency

[Guest]

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

 

[Allen Browne]

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