When creating a Find and Reserve Data model, sometime it returns unexpected results compared to the query run in SQL Server against the source DB tables.
For example, have Orders and OrderItems tables and the relationship between the two tables is Orders.OrderID=OrderItems.OrderID.
In Find and Reserve model, defined columns in the Orders table as model keys, with the OrderItems table as a secondary component of the Find and Reserve model. Defined the relationship in the data model, and using it in the Find and Reserve model. This allows the end user to query on orders, or check "Show Related Tables" and show all orders and their order items (one row for each component associated to an order). However, seeing unexpected data (only 1 row) return from Find and Reserve model when queried in Self-Service Catalog where as the same query returned 4 rows against the DB tables.
The issue is happening with the missing cached data. When the cache update is in the process and if any errors happen during the refresh or a query is run in that time for creating Find and Reserve model, unexpected results will be returned.
For example, Data Prefetch is set to Periodic and the property tdmweb.findReserveService.sync.delay=60 is set in application.properties, the data is going to refreshed every hour and the cashed data is going to be deleted before the refresh. If the data is huge and it takes 20 minutes or so to do the refresh and not all the data is available and returns unexpected results.
Release : 4.9.x
Component : TDM Portal
Recommended to change the property tdmweb.findReserveService.sync.delay=240 instead of 60 or 120 minutes, so the data data refresh happens every 4 hours instead of every hour or two.
Also, for the large tables, recommended to use the Data Prefetch option as OFF, so Source tables can be used instead of the cached table and don't run into the refresh issue and unexpected results issue.
For the smaller tables use the Data Prefetch option On Demand/Periodic.
Data Prefetch documentation is available at https://techdocs.broadcom.com/us/en/ca-enterprise-software/devops/test-data-management/4-9/provisioning-test-data/configure-test-data-reservation-service/configure-dynamic-test-data-reservation-service/data-prefetch.html