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'";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.
SPQuery.ViewAttributes = "Scope='RecursiveAll'";
for (int i = 0; i < 10 ; i++)4. SPQuery query object doesn't need to include
SPQuery oQuery = new SPQuery();
oQuery.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='UserMulti'>"+i +"</Value></Eq></Where>";
SPListItemCollection collListItems = list.GetItems(oQuery);
oQuery.Query = "5. If you want to do CAML query which also consider the time in datetime fields then try this
<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' IncludeTimeValue='TRUE'><Today /></Value></Eq></Where>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
<where><eq><fieldref name="Employee" lookupid="TRUE"><value type="User">1</value></eq></where>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="<FieldRef Name='Description' Nullable='TRUE'/>";To get the item related within past few days , you can use OffsetDays attribute along with Today.
<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime' ><Today OffsetDays='-7' /></Value></Eq></Where>Article:
SPQuery Best Practice