CAML query takes long time to process in C#

Question

I have been searching for ages and trying different solutions but so far nothing makes any difference.

I have a Sharepoint list that has 250-300 items in it. The list has about 40 columns and there is a powerapps form attached to it.

I have a c# application that is trying to interrogate that list to get all of the items that do not have a complete flag set (typically no more than 5-6 items) but the query is taking on average 1min & 40 seconds to complete each time.

I have tried

• List item Using indexes,
• List item I have tried variations of the CAML query (adding and removing row limits and orderby clauses)
• List item I have tried restricting the fields that are returned
• List item I have changed the query so it searches by a different field/field type
• List item I have tried removing the ListItemCollectionPosition lines
• List item and even tried reducing the number of fields it returns to 1 or 2

and nothing seems to make much difference.

Interestingly I have other functions (in the same application) that use exactly the same technique to search for and retrieve one list item from the same list and that works perfectly and really quickly

code that I am using to query:

            var context = getUserContext("Team");

            List oList = context.Web.Lists.GetByTitle("Job Reference List");

            ListItemCollectionPosition itemPosition = null;
            CamlQuery cQry = new CamlQuery();
            cQry.ListItemCollectionPosition = itemPosition;
            cQry.ViewXml = "<View><RowLimit>40</RowLimit><Query><Where><Eq><FieldRef Name=\"WorkComplete\"/><Value Type=\"Integer\">0</Value></Eq></Where><OrderBy><FieldRef Name = 'Created' Ascending = 'False'/></OrderBy></Query></View>";

            ListItemCollection collection = oList.GetItems(cQry);
            context.Load(collection,
                items => items.Include(
                    item => item["Title"],
                    item => item["Customer0"],
                    item => item["Install_x0020_Date"],
                    item => item["gpym"],
                    item => item["Engineer"],
                    item => item["Details"],
                    item => item["Job_x0020_Reference"]
                    ),
                items => items.ListItemCollectionPosition);
            context.ExecuteQuery();

Any ideas as to where I am going wrong, I would be grateful.

Answer 1

Hi, I was using that method to login yes But I've just altered it and setup an AppOnly connection using the pnp.Framework, and now its responding really quickly again. Thanks for all of your help and pointers

Answer 2

Hi @David Evans ,
I have tested your code in a List with more than 40 columns and 300 items.Then the Query completed in a sudden.So I think the issue might relate with the powerapp
form. Could you provide me more details about the powerapp for further test?

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @RaytheonXie_MSFT ,

What do need to know?

The form really just separates the fields into 3 tabs (3 buttons linked to views that have the appearance of tabs) showing different fields based on a Job and how near completion it is, it also has a second list linked into it to show/add comments in sort of a PowerApps Forms version of SharePoints Append comments functionality.

The only other thing is that a customer field is actually a lookup to a different list.

Thanks for your help

Answer 4

Hi, @RaytheonXie_MSFT

For me the ExecuteQuery is where the time is taken, evertything else executes really quickly

Thanks