Here are few key things to remember when implementing SharePoint applications with high volumes of data.
- Never try to override List View Threshold. Keep it to 5000 as it is by default.
- Try avoiding increase of “List View Lookup Threshold”, keep it as 8
- Apply indexes to relevant columns. Be mindful about the content of column before adding indexes
- Use out of the box webparts as much as possible. Utilize parametrized list view webparts, connected webparts etc to achieve advance filtered listings
- Make sure to use indexed columns as much as possible in CAML queries
- Make sure to prioritized CAML query filters depending up on number of items filter out by each clause
- Avoid using People fields for CAML query filtering (anyway those are not index-able)
- Don’t rely on lookup columns heavily.
- Make sure you filter only the required set of list items
- Try using SPListItem.GetItemById whenever an Items being retrieved
- Avoid using SPQuery to make queries, instead user ContentIterator so that there will be less locks in DB
- If required, use caching(possibly ASP.Net Cache) when custom code is being used
- Off load processing from server as much as possible
- Some more tips by Amit Phatak at http://navigatingsharepoint.blogspot.com/2010/08/sharepoint-2010-handling-large-splists.html