1

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.

  • 1
    Hi and welcome! Your question is good, but a bit broad. Do you want all items? Do you want the first x items? Do you want items matching a condition? This information will help answering your question more precisely! – Robert Lindgren Nov 19 '13 at 6:15
  • Hi Robert, Thanks for your reply. There are many ways to get million of items from a list. But I want to get all items in a single shot with much improved performance. – Balaji R Nov 20 '13 at 7:45
  • Sounds like a job for a Content by Search Webpart in 2013, or a custom webpart using a search query. You will need to use paging though. – Robert Lindgren Nov 20 '13 at 8:58
1

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.

Source

Have a look at the following links for better understanding,

  1. http://adicodes.com/sharepoint-2010-list-throtelling/

  2. http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx

  3. http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-change-the-list-view-threshold.aspx

  4. http://msdn.microsoft.com/en-us/library/ff798465.aspx

EDITED:

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.

Source

  • If you find my above answer is correct, please mark it as answer, so that it will be helpful to others :) – Karthik Jaganathan Nov 20 '13 at 6:44
  • Hi Karthik, Thanks for your reply. With the help of throttling, you can specify the maximum amount of items. But I want to get all items in a single shot with maximum performance. – Balaji R Nov 20 '13 at 7:46
  • check the edited section in my answer. – Karthik Jaganathan Nov 20 '13 at 8:54
1

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.

http://msdn.microsoft.com/en-us/library/ff650512.aspx

and

http://msdn.microsoft.com/en-us/library/aa981241.ASPX

or through code use SPQuery to query a list (custom code to suit your needs):

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx

depending on how your list is built and then storing your data within the list will determin the efficency of data retrival!

http://technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx

EDIT

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 ;)

  • Hi, Thanks for your reply.By using the content query webpart, can we get all items in a single shot with maximum performance. – Balaji R Nov 20 '13 at 7:47
  • how many records are you looking to return back from the list? the full million+ or thousands? – Ali Jafer Nov 20 '13 at 9:02
  • No, a content query webpart will still be under the effect of resource throttling. Refer to this previous question – Mario Pereira Dec 8 '13 at 0:51
  • @MarioPereira yes i already know that already! plus the question doesnt say anthing about resource throttling! the default is 5k records. look at this for an understanding technet.microsoft.com/en-us/library/cc262813(v=office.14).aspx iv noted here about it too sharepoint.stackexchange.com/questions/82661/… for large lists they need to be in folders/groups – Ali Jafer Dec 9 '13 at 8:37
  • Good for you Ali! Now worry about answering the OP instead of trying to show me up. The OP asked if he can get back all the items using a content query webpart. I made the safe assumption that the A.) Resource Throttling is on and B.) he is not using folders. And if those items are true which I would bet they are, then no, you won't get back millions of items at once. – Mario Pereira Dec 10 '13 at 4:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.