Iterating SPList.GetItems(SPQuery) very slow and times out

Question

I have a SharePoint 2013 Custom List. It has 3 MMS columns, 3 User Columns and 3 or 4 DateTime columns and a Choice Column and 1 or 2 columns of type Text. This list currently has around 2000 items. I have a CAML query, which is trying to fetch items by checking the value on one of the DateTime columns as less than or equal to Current Date Time. Though SPList.GetItems(SPQuery) executes relatively faster, when I am trying to get the Count or when I am trying to iterate the items, it is too slow and times out. I tried to index the columns (I could index only Non MMS columns), but this had no effect.But if I specify the RowLimit of 10 or 100, it is relatively faster or if I try to bring only ID field (in View), then also, it is faster. This is quite surprising for me, since 2000 items is not at all such a big number. Can anyone please help?

Answer 1

Try using SPList.GetDataTable method

MSDN: https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getdatatable.aspx

public DataTable GetDataTable(
    SPQuery query,
    SPListGetDataTableOptions flags,
    out SPListItemCollectionPosition position
)

With this method you can read with a single query all the information in a c# datatable. Then perform all iterations in memory. The performance is very good.

PS: anyway would be good to check your code to see if anything can be improved.