Portal 4.10.43.0 performance is slow when Confirming tables/Unconfirming tables in the PII Heatmap

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

After upgrading to TDMWeb-4.10.43.0, we noticed that the Portal performance is slow while confirming or unconfirming tables in the PII heatmap view.

The view in Portal has been spinning for over 2 hours just for Unconfirming the tables. Normally, it takes 2-3 minutes. I have tried to refresh the page after 30 minutes or so, and it would have unconfirm a few (5-6 tables) and stop the process, which has to be re-triggered. 

Environment

Release : 4.10

Component : TDM Web Portal

Cause

To troubleshoot the performance issue, place the TDM Portal in debug mode, and then confirm/unconfirm a single table and check the TDMModelingService.log. We should see something similar (notice a 20-second gap between the 3rd and 4th lines in the example provided below). This is the place in the debug log where the query is executed. 

For example:

2022-06-29 16:48:40.631 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, PATCH start /ca/v1/profiler/jobs/547/piidata/251133
2022-06-29 16:48:40.631 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, pii Audit updated /ca/v1/profiler/jobs/547/piidata/251133
2022-06-29 16:48:40.725 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, Get row counts for all tables ahead of time for job: 547 
2022-06-29 16:48:59.620 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, Get counts in gtrep_pii_raw_match for job: 547
2022-06-29 16:48:59.635 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, Now add on tables missed out because there was no PII for job: 547
2022-06-29 16:48:59.666 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, Done with getting PiiData For job: 547 
2022-06-29 16:48:59.682 UTC [DEBUG] [https-openssl-nio-8443-exec-10] --- [U:JBhatt][M:PATCH][P:/api/ca/v1/datamodel/profile/piidata]                       c.c.t.p.c.ResultsController:  RequestID: 217, Module: Profiler_Service, PATCH end /ca/v1/profiler/jobs/547/piidata/251133
 

The time recorded is the time just before and after the query is executed, and should give a good indication of how long it took the Database to process the request. Keep in mind, that this example is for a single table, if you are confirming or unconfirming 10 tables, then our delay would be 20 seconds times 10 tables, or 200 seconds total.

If you are seeing a delay, the best way to troubleshoot is to work with your DBA and have them perform a SQL Profiler trace while you confirm/unconfirm a table to see what exactly is happening on the database side. 

Resolution

After the SQL Server DBA rebooted the SQL Server machine, the performance improved, and the delay was not seen.