question

DavidEvans-0686 avatar image
0 Votes"
DavidEvans-0686 asked RaytheonXie-MSFT commented

CAML query takes long time to process in C#

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.









sharepoint-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidEvans-0686 ,
would you please provide us with an update on the status of your issue?

0 Votes 0 ·
DavidEvans-0686 avatar image
0 Votes"
DavidEvans-0686 answered RaytheonXie-MSFT commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidEvans-0686 ,
Glad that you found the solution to this problem. Thanks for your sharing.

You could accept your answer via the "Accept Answer" button, it would be helpful to others who have similar issue in the future.

0 Votes 0 ·
RaytheonXie-MSFT avatar image
0 Votes"
RaytheonXie-MSFT answered

Hi @DavidEvans-0686 ,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidEvans-0686 avatar image
0 Votes"
DavidEvans-0686 answered RaytheonXie-MSFT commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidEvans-0686 ,
Could you use debug to confirm which process takes too long time? The ExecuteQuery or the camlquery? In my case, The query runs fast in a similar list.

0 Votes 0 ·
DavidEvans-0686 avatar image
0 Votes"
DavidEvans-0686 answered RaytheonXie-MSFT converted comment to answer

Hi, @RaytheonXie-MSFT

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

Thanks

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidEvans-0686 ,
Could you provide the type of the columns you load?

0 Votes 0 ·

Hi the query uses these columns:

  • Title - Default field created when the list was created - text field

  • Customer0 - Lookup to a second list on another subsite

  • Install_x0020_Date - Date Field

  • gpym - Text field

  • Engineer - Person or group field

  • Details - Multiple lines of plain text

  • Job_x0020_Reference - Hyperlink field

Thanks for your help










0 Votes 0 ·

Hi @DavidEvans-0686 ,
Per my test, I can't reproduce your issue. If you try to remove all columns and just leave "Title" to load. Will the process still take minutes to run?

0 Votes 0 ·
Show more comments