SPQuery Few important things

1. Always specify the RowLimit to get limited results.

1. oQuery.RowLimit = 100;

2. To Get the Search Items from the sub folders of the list using SPQuery you can do following. Check here

1. SPQuery.ViewAttributes = "Scope='Recursive'";

2. or

3. SPQuery.ViewAttributes = "Scope='RecursiveAll'";

3. Be sure to create a new SPQuery instance each time you use it. You cannot create one SPQuery instance and then reuse it multiple times in a loop where you alter the SPQuery's Query value.

1. for (int i = 0; i < 10 ; i++)

2.

3. SPQuery oQuery = new SPQuery();

4. oQuery.Query = ""+i +"Value>Eq>Where>";

5. SPListItemCollection collListItems = list.GetItems(oQuery);

4. SPQuery query object doesn't need to include tags

1. oQuery.Query = " name="Status"> type="Text">Completed";

5. If you want to do CAML query which also consider the time in datetime fields then try this

1. oQuery.Query = " Name='Created'/> Type='DateTime' IncludeTimeValue='TRUE'> />Value>

6. SPQuery can't be used to search across site collection.One can use SPSiteDataQuery class to search across multiple Lists.

To query the lookup based value items

1. oQuery.Query = " name="Employee" lookupid="TRUE"> type="User">1";

7. To restrict the items to approved, use moderationtype attribute. more details here

1. SPQuery.ViewAttributes = "Scope='Recursive' ModerationType='HideUnapproved'";

8. If you want your query to return empty columns, you have to add Nullable='TRUE' to the viewfields.

1. query.ViewFields="";

To get the item related within past few days , you can use OffsetDays attribute along with Today.

1. query.Query = "";

Article:
SPQuery Best Practice