2

I am facing a List Throttling issue on a production site following are the details.

I have a SharePoint Calendar list named "Bookings" it currently has about 16,000 events(items) and the list view threshold for normal users is set to 5000 and for site collection administrators is set to 20,000.(the default values in central admin)

So here when i query this list it gives me a Throttling exception as it should do because when a particular list crosses throttling limits we cannot Query all the items at once.

Following is my code

SPListItemCollection collBookings = null;

 SPList lstBookings = web.Lists.TryGetList("Bookings");

   qryBookings.ViewFields = "<FieldRef Name='Title'/>" +
                                       "<FieldRef Name='ID'/>" +
                                       "<FieldRef Name='EventDate'/>" +
                                       "<FieldRef Name='Room_x003a_MaxCapacity'/>" +
                                       "<FieldRef Name='Company'/>" +
                                       "<FieldRef Name='Room'/>" +
                                       "<FieldRef Name='Place'/>" +
                                       "<FieldRef Name='Hosted_x0020_By'/>" +
                                       "<FieldRef Name='Author'/>" +
                                       "<FieldRef Name='Linked_x0020_Id'/>" +
                                       "<FieldRef Name='Is_x0020_Linked'/>" +
                                       "<FieldRef Name='fRecurrence'/>" +
                                       "<FieldRef Name='Internal_x0020_Meeting'/>" +
                                       "<FieldRef Name='RoomMaxCapacity'/>" +
                                       "<FieldRef Name='Total_x0020_Attendees'/>" +
                                       "<FieldRef Name='External_x0020_Attendees'/>" +
                                       "<FieldRef Name='EndDate'/>";

                    qryBookings.Query = "<Where>" +
                                    "<DateRangesOverlap><FieldRef Name='EventDate' />" +
                                    "<FieldRef Name='EndDate' />" +
                                    "<FieldRef Name='RecurrenceID' />" +
                                    "<Value Type='DateTime'><Today /></Value></DateRangesOverlap></Where>";

                    qryBookings.ExpandRecurrence = true;
                    qryBookings.CalendarDate = date.Date;


                    collBookings = lstBookings.GetItems(qryBookings);

So what i did was, i used Paging with pagelimit=4000 so that only 4000 items are queried at one time

Following was my new code

qryBookings.RowLimit=4000;
                    do{
                          qryBookings.ViewFields = "<FieldRef Name='Title'/>" +
                                       "<FieldRef Name='ID'/>" +
                                       "<FieldRef Name='EventDate'/>" +
                                       "<FieldRef Name='Room_x003a_MaxCapacity'/>" +
                                       "<FieldRef Name='Company'/>" +
                                       "<FieldRef Name='Room'/>" +
                                       "<FieldRef Name='Place'/>" +
                                       "<FieldRef Name='Hosted_x0020_By'/>" +
                                       "<FieldRef Name='Author'/>" +
                                       "<FieldRef Name='Linked_x0020_Id'/>" +
                                       "<FieldRef Name='Is_x0020_Linked'/>" +
                                       "<FieldRef Name='fRecurrence'/>" +
                                       "<FieldRef Name='Internal_x0020_Meeting'/>" +
                                       "<FieldRef Name='RoomMaxCapacity'/>" +
                                       "<FieldRef Name='Total_x0020_Attendees'/>" +
                                       "<FieldRef Name='External_x0020_Attendees'/>" +
                                       "<FieldRef Name='EndDate'/>";

                    qryBookings.Query = "<Where>" +
                                    "<DateRangesOverlap><FieldRef Name='EventDate' />" +
                                    "<FieldRef Name='EndDate' />" +
                                    "<FieldRef Name='RecurrenceID' />" +
                                    "<Value Type='DateTime'><Today /></Value></DateRangesOverlap></Where>";

                    qryBookings.ExpandRecurrence = true;
                    qryBookings.CalendarDate = date.Date;


                    collBookings = lstBookings.GetItems(qryBookings);


                      qryBookings.ListItemCollectionPosition = collBookings.ListItemCollectionPosition;
                    } while (qryBookings.ListItemCollectionPosition != null);

But Even after doing this i got the Throttling Exception.

So i think we cannot query to recurring events if threshold limits are exceeded because, i have tried using paging on a custom list whose throttling limits have exceeded and it has worked.

Can Someone help me resolve this issue.

Thanks

  • You can try disabling the throttling on that list – Aanchal Jun 25 '14 at 15:17
  • But is disabling throttling a good option? – luckydeveloper Jun 26 '14 at 7:15
  • You can check if throttling is the issue?? Then you can increase this limit from central admin. – Aanchal Jun 26 '14 at 9:04
  • Set ExpandRecurrence to false, then try again to see if that gives you any clue. – Paiman Samadian Jun 27 '14 at 3:13
1

I did some research on this sometime ago. Simply a listview threshold means the number of rows a single SQL query will consume. If you use a lookup, person or datetime column the sql table creates a join query with all the rows in the current table. Which means even you end of the day returns only 4000 items, it create a table of 16000 and do the query. This works 4000 and 4000 if it is a text or boolean column as I found. They do not need joining the whole table before query.

I am not much updated on how SharePoint handle SQL paging.

On the other hand if you do not hit the query too much, you better disable throttling.

Another great option would be splitting the list vertically. Which means something like one calendar for one year. Then you can write a custom web part to display data by joining all of them. This gives much flexibility as I see.

0

Can you try running your query using caml query builder? You will have to index your columns on which you are querying.

-1

Put for example

 qryBookings.RowLimit=1999; 

and run it again. it works!

  • I think the intention is to still be able to access all of the results, won't this limit the total amount of results returned to 1999 (with no option to page to the rest)? – John-M Aug 19 '14 at 0:48

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.