What is a large list? What might be the problem if I store 1 million documents in a document library? Is there an improvement from WSS 3 to SharePoint Foundation?
If you want answers to the questions, take a look at a great post at the “Microsoft Enterprise Content Management (ECM) Team Blog”.
A summary to the problem:
Any time you ask for content from SharePoint, you have to specify how it’s sorted – for example, the All Documents view in SharePoint 2007 asks for the top 100 results, sorted by filename. But items aren’t sorted by filename in the SharePoint content database – so, to bring you this view, SharePoint has to gather up all these million items, sort them, and finally display the 100 ones at the top of the sorted list.
Migrate to SharePoint 2010 quickly to use its new features which help you dealing with large lists.