Document query very slow with 1000 documents

[Mahmoud_Audu1]

Hello,

 I have an Ai call that retrieves about 1000+ documents  (and i am getting back 1000+

 docs) and checks for a keyword, if it find it it moves to a document exist page, if not it creates a new form.

However, when I execute this, my code takes 1:30+ mins to just fetch and perform this check, is there any way to optimize this as it is affecting our end user

Thank you  (see code below)

         // code to perform doc query       DocumentList documents = docQuery.Execute(1000);                // Check if user already exist                foreach (Document doc in documents)                {                    KeywordRecordList keyRecList = doc.KeywordRecords;                    foreach (KeywordRecord keyRec in keyRecList)                    {                        foreach (Keyword key in keyRec.Keywords)                        {                            if (key.KeywordType.Name == "S#")                            {                                if (key.Value.ToString() == user.userNumber)                                {                                    this.TempData["docId"] = doc.ID;                                    return this.RedirectToAction("FormExists");                                }                                break;                            }                        }                    }                }            }// create new form

[Thilina_Random2 · Accepted solution]

You have a lot of for loops. From a code perspective, this might make things easier. Pls note that there could also be other issues causing slowness. 

KeywordType keywordType = unityApp.Core.KeywordTypes.Find("S#");If (keywordType == null){  throw new Exception("KeywordType not found.");}Keyword searchKeyword = keywordType.CreateKeyword(user.userNumber);DocumentQuery docQuery = unityApp.Core.CreateDocumentQuery();docQuery.AddKeyword(searchKeyword);DocumentList documents = docQuery.Execute(1000);

If the "documents" list has >= 1 items, you can perform your logic to add to the "TempData" array and redirect.

[Mahmoud_Audu1]

This answer worked beautifully. 

Thank you 

my code snippet now looks like this

DocumentQuery docQuery = app.Core.CreateDocumentQuery();
KeywordType keywordType = app.Core.KeywordTypes.Find("S#");
if (keywordType == null)
{
this.logger.Error("KeywordType S# not found.");
}

Keyword searchKeyword = keywordType.CreateKeyword(user.userNumber);
DocumentType documentType = app.Core.DocumentTypes.Find("STU SAS Intake Form (Unity Form)");
docQuery.AddDocumentType(documentType).AddKeyword(searchKeyword);
DocumentList documents = docQuery.Execute(1000);

// Check if user already exist, I am keeping the loop as some users have multiple forms but we only need the lastest

// OnBase does this by default but you can add documents.OrderByDescending(m => m.DocumentDate);

// to sort by date

foreach (Document doc in documents)
{
    // check if a document exist
   if  (doc.ID != 0)
   { 
         this.TempData["docId"] = doc.ID;
         return this.RedirectToAction("FormExists");
     }
}

//create new form

Hope this helps someone as well

[Thilina_Random2]

Glad to hear that it worked!

[Justin_Carlson]

This is most likely not because the query is taking that long, but because by default document queries do not return back keyword information. So when looping through the results and checking a keyword on every document, it causes a round trip to the server for each document, which is why you see the length of time it takes increase with the number of results. 

I would recommend using the AddDisplayColumn() method off of the document query object to add the keyword type you would like to check. Then you must use the ExecuteResult() method rather than Execute() to leverage the display column in the results.

This approach will include the keyword value as a display column in the results, which will avoid the round trip for each document object and speed your code up significantly.

Two side notes:

1. I believe if a document has more than one value for the keyword you add as a display column, it will show up multiple times as a result object (once per value) - similar to how custom queries work. So if this is a possibility, make sure to account/test for it.
2. An alternate to what I have suggested here is to simply set the RetrievalOptions property of the document query object to LoadKeywords. This would force the keywords to be returned in one round trip to the server when executing the query - however it loads all the keywords for all the documents - so it wouldn't require as much changes to your code, and it would speed things up a bit, the approach I described would speed things up a lot more and scale better.