SPQuery Few Important Things

SPQuery Few important things
1. Always specify the RowLimit to get limited results.

 oQuery.RowLimit = 100;

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

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

 or

 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.

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

{
 SPQuery oQuery = new SPQuery();

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

 SPListItemCollection collListItems = list.GetItems(oQuery);
}
4. SPQuery query object doesn't need to include tags

 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

 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

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

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

 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.

 query.ViewFields="";

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

 query.Query = "";

Article:
SPQuery Best Practice