Can we query recurrent events in SharePoint calendar list if List throttling limit is exceeded?

Question

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

Answer 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.

Answer 2

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

Answer 3

Put for example

 qryBookings.RowLimit=1999; 

and run it again. it works!