I have done some analysis of this API, and main culprit of performance is that there are huge number of DB calls being made during this API execution which we should be able to reduce. Also we don't have caching mechanism right now, there are many redundant queries we are doing around users and user associations which ideally should be cached instead of fetching those details everytime from DB, that will hugely improve this performance. Another performance impact is that we are sending Emails synchronously as part of this API call, below is the breakdown:

CreateDocument

5523ms: var SrcProductData = commonRoutines.GetLocalizedMetaData(LookupTypeEnum.sourceproduct.ToString(), this.sessionData.LanguageID);

Inside GetLocalizedMetaData multiple Database queries are being run. Two queries to get code list, if there are N codes then inside a for loop separate DB queries are being run for N times to get data individually. This is huge number of DB queries to get localized data. I will recommend to complete this operation in one DB call and also this data can be cached. But before that, effort here seems to get the sourceProductId, for which a single query can be drafted.

3331ms: responseMessagesList = ValidateData(dmsFields, candidateData);

Here also we see at-least two separate DB queries being fired to validate data, for some case it will again call GetLocalizedMetaData which itself fires multiple DB queries. We will need to simplify this validation logic also here. Caching should also be introduced for frequently used person detail fields.

21946ms: ProcessData(candidateData.Email, dmsFields, candidateData, ref mappedFields); 

This method usages a helper function to get personId from Email which itself fires 3 DB queries, first this can be simplified to get this in single DB call, and on top of that, for this kind of helper methods we should have caching. After that "objEditForms.ProcessPDFData(dmsFieldsDictionary, personId, dynamicPayload.RequisitionId);", this method itself makes multiple DB calls to update person data, this kind of updates can also be moved to SP for better performance.

19755ms: responseMessagesList = PreviewAndSendEmail(candidateData);

Here again we make use of "GetPersonIdFromEmail" helper methods which itself is making multiple DB calls. In "SendFormToSingleCandidate", we are creating document and again making query to get Id for that document, this can be simplified.  There are many more DB queries being executed in separate statements, we can consider moving to SP. Also we are sending Email synchronously which is also blocking the execution untill the email gets sent, this we can move to asynchronous execution.

IntegrationBaseController(5634ms): 

There are multiple separate DB queries being exuected here for each API which is called through OneIAM. Preferable we should have caching for common user properties and associations. WE can consider modifing logic to reduce number of DB calls to accomplish same validation or moving validation where multiple DB calls are required to a SP.

Hi Swapnil Pandhare,

Please resolve these DB call issue. Let me know if you need a call to go through this

Regards,

Samir

Hi All,

Below is the update till 6th may : 

commonRoutines.GetLocalizedMetaData(LookupTypeEnum.sourceproduct.ToString(), this.sessionData.LanguageID);

• Multiple queries and loops removed from this method and created a single query. This is called on almost every page from UI and Integration API requests.

3331ms: responseMessagesList = ValidateData(dmsFields, candidateData);

• Multiple queries and loops removed from *GetLocalizedMetaData*method and created a single query. This is called on almost every page from UI and Integration API requests.

19755ms: responseMessagesList = PreviewAndSendEmail(candidateData);

 "GetPersonIdFromEmail" -Removed multiple DB calls from this routine.

In "SendFormToSingleCandidate" - Simplified the query to get documentid. Also, now emails are sent asynchronously.

PR : https://github.com/cbdr/DMS/pull/553

As per discussion with Harshveer Singh, No action required from QA end. So, marking this jira done.

Samir Sachin Hingole Hrishikesh Deshpande Rohan J Khandave