Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am working on Sharepoint 2010. I want to get 500000 list items from the "Announcement" list. I got server timeout issue and the code is given below

SPList list = web.Lists["Announcements"];
SPQuery query= new SPQuery();
query.Query = "<Where><And><Geq><FieldRef Name=\"ID\" />
    <Value Type=\"Counter\"> 1</Value></Geq><Leq><FieldRef Name=\"ID\" />
    <Value Type=\"Counter\">500000 </Value></Leq></And></Where>";
query.RowLimit = 500000;
SPListItemCollection items = list.GetItems(query);
DataTable dt = items.GetDataTable(); //Here I got the Time out error

Could anyone help me resolve this issue?

share|improve this question
3  
How is your large list throttling configured on your farm? –  webdes03 Sep 29 '11 at 16:42
add comment

2 Answers

This could be most likely resolved by increase of throttling limit, but...

Quote form Best Practices: Common Coding Issues When Using the SharePoint Object Model

If the list contains more than 2,000 items, you will need to paginate the list in increments of no more than 2,000 items.

So when working with large lists (over 2000 items) you need to optimize your code for better performance. If you follow suggestions from Best Practices for LARGE SharePoint Lists and Documents Libraries your code should look like this:

DataTable dt = null;
SPList list = web.Lists["Announcements"];
SPQuery query= new SPQuery();
query.Query = "<Where><And><Geq><FieldRef Name=\"ID\" />
     <Value Type=\"Counter\"> 1</Value></Geq><Leq><FieldRef Name=\"ID\" />
     <Value Type=\"Counter\">500000</Value></Leq></And></Where>";
query.RowLimit = 2000;
do 
{ 
    SPListItemCollection myItems = list.GetItems(query); 
    if(dt == null)
    {
        dt = myItems.GetDataTable();
    }
    else
    {
        dt.Merge(myItems.GetDataTable());
    }

    query.ListItemCollectionPosition = myItems.ListItemCollectionPosition;

} while (query.ListItemCollectionPosition != null);

(I have tested this on smaller list)

Some more reading material on working with large lists can be found here:

Managing Lists and Libraries with Thousands or Millions of Items

I really wonder why do you need to put so many records in table.

Addition:

Slowest command in above code is myItems.GetDataTable(). To speed things up a bit there is possibility to filter fields (columns) returned by SPQuery. By doing so datatable will contain only desired columns. So after query.Query line put:

query.ViewFields = "<FieldRef Name='AssignedTo' /><FieldRef Name='LinkTitle' />";

Of course this is just example and you need to include 'real' fields you need.

Further more you can 'play' around with RowLimit (increase/decrease) to see if it helps.

share|improve this answer
    
ok, it's work for me, thanks –  Rasu Sep 30 '11 at 6:37
    
Great. if you find my answer to be helpful accept it as an answer and/or vote it up FAQ: How to ask –  Vedran Rasol Sep 30 '11 at 7:41
    
ok, it's work for me, thanks, but it's taking 4 minutes to finish this opertaion, is it possible to minimize the time upto 1 minute –  Rasu Sep 30 '11 at 9:43
    
@Rasu I have updated my answer –  Vedran Rasol Oct 2 '11 at 21:34
add comment

You can use ContentIterator class.

It provides helper methods that ensure lists can be queried while regulating the amount of data being transferred. This is especially important for queries on large lists so that the load put on the database is not excessive and hence helps to avoid the code throwing a SPQueryThrottledException.

For more details on Content Iterator :

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

http://extreme-sharepoint.com/2012/07/17/data-access-via-caml-queries/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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