SPQuery with Pagination

Today, I have come across with performance issue while querying items from a list, Where my list contains nearly 1000 items. So, to resolve this it's better to query some no.of items instead of querying all items by using RowLimit property of SPQuery class. But we can query all the time 'n' number of items only, means suppose you have given RowLimit is 100 then you will get 100 items only, so next time if you want items from 101 you have to use SPListItemCollectionPostion class. Using these both properties we can implement paging in the same way how the list is offering paging to us. I have found following good article on this.

http://blogs.msdn.com/b/colbyafrica/archive/2009/02/19/learning-sharepoint-part-vi-list-pagination.aspx

One more important thing is, in SharePoint 2010 we will not be able to query more than 5000 items at a time due to list view threshold limit. The following code snippet helps you to query all items and keep it in datatable.


SPListItemCollection itemColl = null;
DataTable dtApprovals = null;
SPQuery qryCVVApp = new SPQuery();
qryCVVApp.RowLimit = 2000;
do
{
                itemColl = lstCVVApprovals.GetItems(qryCVVApp);
                if (itemColl.Count > 0)
                {
                    DataTable dt = itemColl.GetDataTable();
                    if (dtApprovals == null)
                        dtApprovals = dt.Copy();
                    else
                        dtApprovals.Merge(dt);
                }
                qryCVVApp.ListItemCollectionPosition = itemColl.ListItemCollectionPosition;
} while (qryCVVApp.ListItemCollectionPosition != null);