Status history APIs like:

/api/documents/{sourceProduct}/{documentId}/{personId}/statusHistory

/api/documents/{sourceProduct}/document/{id}/statusHistory

Which are calling GetDocumentStatusData internally have performance issue. In GetDocumentStatusData there is for loop which is iterating over all the document list, inside which are making DB connections. e.g. if there are 1000 documents, there will 1000 DB connection made. Then Again within this for loop, we call CreateCompletedDocPreviewLink, which again has a for loop inside which DB connections are being made for each templateId.

With around 30 documents, API response time is 911ms, and with around 600 documents, API response time is 21822ms, which clearly shows that with increasing number of documents, API is slowing down.

Recommendation is to get all document's status in single DB call and introduce pagination instead of fetching all documents at once. Also DB indexes need to be added to optimize the query.

And also, there should be some pagination instead of returning complete history in single response.

Tasks to be done to complete this:

1. Get all document status in single DB Call.
2. Introduce pagination instead of fetching all documents.
3. Optimize other DB queries being done.
4. Add database indexes to optimize these queries.