Best Practices for large SharePoint Lists and documents Libraries
SharePoint lists and document libraries are not designed to handle large numbers of items, in fact as soon as you add more than a few thousand you will see a warning within the List Settings Page that says:
This list or library contains a large number of items. Learn about managing a large list or library and ensuring that items display quickly.
As part of my product suite I consistently have lists with hundreds of thousands of items in them and have learned many valuable lessons in making large numbers of items workable.
1. First off do not use the DataView webpart (from the SharePoint designer) or any xml based rendering webpart as they first grab all the items in the list and then apply the XLST to render and you will face both memory and huge performance problems. The regular list webpart works fine even for large datasets, but you will not be able to get total list counts regardless.
2. If you are planning on sorting or filtering by any columns be sure to add them to the list’s indexed fields in the list settings page.