As with many other frameworks, you need a bit of insight on internal processes to create effective solutions.
Sitecore Query is XPath-like query language that allows both simple 'by name' or 'by path' resolutions and complex expressions. What is special about it is that query can be handled either directly in the database on data provider level or by the data manager tier. When the query is being resolved, data manager tries to use its data providers first and resorts to higher level (read: slower) API in case of failure.
Sql server data provider (as well as the other standard providers I think) supports only a small subset of query: '/sitecore/content/home' - resolving item by path and '//home' - resolving item by name.
What does this really mean? Imagine that you have a large site with sql server database backend and you're trying to find some content items. For most simple scenario, we need to find all content items named 'needle':
Item content = Sitecore.Context.Database.Items["/sitecore/content"];
Item[] needles = content.Axes.SelectItems("//needle");
Sql server data provider supports this kind of query, so the query gets resolved directly in the database fairly fast even though we have a large number of content items.
Then we complicate the requirements a bit: say our items have 'IsHidden' checkbox field and we only want needles that are not hidden:
Item content = Sitecore.Context.Database.Items["/sitecore/content"];
Item[] needles = content.Axes.SelectItems("//needle[@IsHidden != 1]");
Predicates are not supported by the sql server data provider, so it ignores the query. We don't have any other data providers in our database, so as I said earlier data manager resorts to higher level query api. This basically means that all items in the query scope (all descendants of /sitecore/content in this example) are loaded so that predicate can be evaluated against each item and the matching items are returned.
The difference between database and item evaluation can be quite dramatic. Now understanding how data manager and data providers work together to evaluate the query, lets improve our solution:
Item content = Sitecore.Context.Database.Items["/sitecore/content"];
// Limit ourselves to dataprovider-supported query
Item[] allNeedles = content.Axes.SelectItems("//needle");
// Do the additional filtering - [@isHidden != "1"] predicate evaluation.
List<Item> nonHiddenNeedles = new List<Item>();
foreach(Item item in allNeedles)
{
if (item["IsHidden"] != "1")
{
nonHiddenNeedles.Add(item);
}
}
By using '//needle' query we obtain all items named 'needle' in a very efficient way because the query is resolved in the database. There are probably only a few such items, so iterating through each of them to check the 'isHidden' field should also be fairly inexpensive and it only requires a few additional lines of code.
Sql server data provider will be able to support field value predicates soon, but the point remains the same: understand what kinds of query are supported by your database backend when querying against large item sets.
And there's another one: when creating data providers, support common query scenarios to avoid performance downfalls.