FDM cross reference call to SQL Server is extremely slow

book

Article ID: 140830

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Using FDM cross mapping in SQL Server and doing the x-mapping for lookup table having 960 000 rows is extremely slow. One call executed on the query browser (any sql editor) takes some milliseconds, but the same call from FDM will take over 100ms. When watching from SQL Server Profiler we can see that the call goes thru "exec sp_execute" where enormous amount of pages are fetched: 10470 vs. 3 pages in direct call. 

Our lookup table is like this: 

CREATE TABLE csg_ca_xref (

rx_ref_id VARCHAR(12) COLLATE Finnish_Swedish_CI_AS NOT NULL, 

rx_old_value VARCHAR(60) COLLATE Finnish_Swedish_CI_AS NOT NULL, 

rx_new_value VARCHAR(60) COLLATE Finnish_Swedish_CI_AS, 

rx_date_created DATETIME DEFAULT getdate(), 

CONSTRAINT csg_ca_xref_pk PRIMARY KEY (rx_ref_id, rx_old_value));

Is there a way to control how these calls are handled? They shouldn't go thru "exec sp_execute" in the database.

Cause

We intentionally use prepared statements and then execute as we need to run queries based on different values. This is a faster approach.

Environment

Release : 4.8

Component : CA Test Data Manager

Resolution

We intentionally use prepared statements and then execute as we need to run queries based on different values. This is a faster approach.

You would definitely get some benefit by using FASTXREF provided both xref table and table to be masked are in the same DB server machine.