What is the best way to access the millions of items in a share point list? I've a list and it contains a millions of records. I want to get the items from a list. How to achieve it.
SharePoint large list and libraries operations like Delete/Update are one of the top performance killers.
What can we do if the server is overloaded when we access large lists and libraries? One solution is list view throttling.
A. List view throttling
List view throttling protects the server from unintentional load by limiting the number of list items returned per view. When the list view is greater than throttle limit, users receive a message that all the data cannot be retrieved.
Resource throttling monitors CPU %, Available Memory, ASP.NET Queue and Wait time in Queue. Every 5 seconds a timer job is checking resources. Throttling will begin after 3 unsuccessful checks. This throttling period will end after one successful check of the resources.
During a period of active throttling, HTTP GET requests and Search Robot requests will generate a 503 error and will be logged in the event viewer and no new timer jobs will begin.
Resource throttling counters can be changed via PowerShell commands.
Activate List throttling
Remark: Is recommended to configure at the Web Application level
B. SharePoint indexed columns
Another important step to improve list performance is indexing SharePoint columns. This option with query throttling strategies can help you to improve the performance of large lists.
You can index up to 20 columns but be aware that not all of the columns types are supported to be indexed. Here you have a complete list.
Activate Indexed columns
Remark: Before applying the following operations be sure you are doing them during the “Daily time Window” if List throttling is active, because creating an index requires accessing all the items in the list.
Have a look at the following links for better understanding,
If you want to get all the items from the list then SharePoint Server 2010 provides a new class named ContentIterator that you can use to query lists without hitting the limits set using throttling. You should consider using this class if you need to run a query that will return more than 5,000 rows of data.
to answer your question, use the pre built webpart:
Content Query Webpart
The ContentByQueryWebPart, commonly referred to as the Content Query Web Part, is a part of the Enterprise Content Management (ECM) functionality in Microsoft Office SharePoint Server. It aggregates and displays list items within a site hierarchy. In addition to hierarchical query capabilities, the Content Query Web Part provides caching and query optimization for the SPSiteDataQuery object that it contains. These optimizations have better performance than if you directly call methods of the SPSiteDataQuery object. You can also use SharePoint Designer to customize the aggregation and display capabilities of the Content Query Web Part with little or no coding.
or through code use SPQuery to query a list (custom code to suit your needs):
depending on how your list is built and then storing your data within the list will determin the efficency of data retrival!
AS noted above you can use the CQWP to get the list of items as its faster then doing it yourself, it does things behind the scene that we dont see like cashing ect...
now onto resource throttling, yes its on and its on most if not all big lists by default due to effecting performance on large list retrival between the sql server and sharepoint server and then the cashing of data in ram which yes would be faster after the first call but will eat up your resources ;).
If your adament to make no throttling on a specified list and its a company requirement to have over 1million recods returned than you do have the option to turn off throttling on a single list using powershell:
$web = Get-SPWeb -Identity http://YourSiteURL/sites/SubSite $web.AllowUnsafeUpdates = $True $list = $web.Lists["YOURlist"] $list.EnableThrottling = $False $list.Update() $web.AllowUnsafeUpdates = $False $list.EnableThrottling $web.Update() $web.Dispose()
i hope this answers your question and others ;)