SharePoint: Efficient way to retrieve all list items

Question

I am retrieving all items from list containing around 4000 items. But it seems to take longer time to fetch all items which is ~15 to ~22 seconds. Is there any best way to fetch all items from list in negligible time?
Following is the code i am using to fetch all items:

 using (SPSite spSite = new SPSite(site))
            {
                using (SPWeb web = spSite.OpenWeb())
                {
                    list = web.Lists["ListName"];
                    SPQuery query1 = new SPQuery();
                    string query = "<View>";
                    query += "<ViewFields>";
                    query += "<FieldRef Name='ID' />";
                    query += "<FieldRef Name='Title' />";                    
                    query += "</ViewFields>";
                    query += "<Query>";
                    query += "<Where>";
                    query += "<Eq>";
                    query += "<FieldRef Name='ColName'></FieldRef>";
                    query += "<Value Type='Boolean'>1</Value>";
                    query += "</Eq>";
                    query += "</Where>";
                    query += "</Query>";

                    query += "</View>";
                    query1.Query = query;
                    SPListItemCollection listItems = list.GetItems(query1);
                }
            }

I think using CAML query i a best way to fetch the list items fastly. — Mirza Danish Baig, May 14 '15 at 08:03
Yes it is best way, but it is taking too much time. Do you know any workaround to reduce fetching time? — Ashish, May 14 '15 at 11:18

score 0 · Answer 1 · answered May 21 '15 at 21:02

Normally when it is taking this long to Retrieve items you are hitting a Boundary or limit.

First up you need to test putting a limit of your query, so you return less than 2000 items, or until you find when it starts becoming unbelievably slow.

Then you need to see if you can break your query up, or do multiple queries to get your items depending on this figure.

Cheers

Truez

score 0 · Answer 2 · answered May 21 '15 at 21:35

Fetching many items in one shot is for sure not the best practice, or suggested way.

You have to look into alternative options, like

Column indexes: related to the version of SharePoint you're using; evaluate and test if really can give some benefits in your case
Split fetch data queries into multiple queries, by finding a group by that suits best to your data, together with the threshold. In this way you can run the queries in parallel, and likely see performance benefits
Use Search: rely on the SharePoint Search engine, quite different between SharePoint versions, but for sure this will result to be blazing fast in comparison to SPQuery. With the downsides of having to rely on search crawl schedules for getting up-to-date data

SharePoint: Efficient way to retrieve all list items

2 Answers2