Publishing Performance Example

The development team performed testing to show performance benchmarks for publishing to CSV and XLSX files. You can use these benchmarks to help tune the performance of your system.
tdm49
The development team performed testing to show performance benchmarks for publishing to CSV and XLSX files. You can use these benchmarks to help tune the performance of your system.
Environment Details
The following environment was set up to gather performance data for publishing to CSV and XLSX files.
Machine specs
The machine has 16 GB physical memory, 4 vCPU, and runs Microsoft Windows Server 2012 R2 DataCenter. The repository was on a local SQL Server 2016.
performmachine.jpg
Table used for the performance publish
CREATE TABLE [dbo].[CARD_ACCOUNT]( [CARD_ID] [int] NOT NULL, [CARD_BA_ID] [int] NOT NULL, [CARD_RA_ID] [int] NOT NULL, [CARD_BCH_ID] [int] NULL, [CARD_ICH_ID] [int] NULL, [CARD_NO] [varchar](16) NOT NULL, [CARD_EXP_DATE] [varchar](4) NOT NULL, [CARD_VALID_DATE] [varchar](4) NOT NULL, [CARD_NAME] [varchar](30) NOT NULL, [CARD_CVV] [decimal](4, 0) NOT NULL, [CARD_PRI] [varchar](3) NULL, [CARD_SUP] [varchar](3) NULL, [CARD_ADD] [varchar](3) NULL )
Test 1 - Hard-coded Data
No expressions were used. All data were hard-coded in the generator.
Publish to CSV
repeater
performance based on number of rows
10,000
0.5 seconds
100,000
1.2 seconds
1,000,000
7.6 seconds
10,000,000
1 min 08 seconds
100,000,000
11 min 48 seconds
Publish to XLSX
Publish to XLSX is memory intensive compared to publish to CSV. Using the default CA TDM Portal configuration, the publish hit a wall around 300,000 counts when the CPU usage went high and stayed high. In fact, performance started degrading around 260,000 counts.
This behavior is caused by the GC (Garbage collector) going overdrive when trying to clean up some memory to make sure that the Portal application does not crash with an out-of-memory exception.
The following graph outlines the impact of garbage collection on the Portal:
xlsxpublish.jpg
Before starting a high-volume publish to XLSX, make sure you increase the memory that is allocated to the Portal.
Edit the YASJW config file called
wrapper.conf
located under
CA\CA Test Data Manager Portal\service\conf
. You can set either
maxmemory
or
maxmemory.percent
. With
maxmemory.percent
, the maximum allocated memory is calculated from the number that was set, times the physical memory.
repeater
performance based on number of rows
100,000
32 seconds
200,000
59 seconds
300,000
1 min 30 seconds
400,000
1 min 49 seconds
500,000
2 min 40 seconds
600,000
3 min 36 seconds
Test 2 - One Expression
We publish using one expression ~NEXT~ in the generator.
Publish to CSV
repeater
performance based on number of rows
1,000,000
14 seconds
10,000,000
1 min 29 seconds
100,000,000
12 min 38 seconds
Publish to XLSX
repeater
performance based on number of rows
100,000
24 seconds
200,000
46 seconds
300,000
1 min 18 seconds
400,000
1 min 47 seconds
500,000
2 min 17 seconds
600,000
2 min 41 seconds
700,000
3 min 16 seconds
SQL Server Target publish (default config)
repeater
performance based on number of rows
100,000
4 min 18 seconds
200,000
8 min 16 seconds
400,000
17 min 36 seconds
(portal restarted)
800,000
23 min 12 seconds
SQL Server Target publish (iterationsBeforeCommit=20000)
tdmweb.publish.batchCommit=true
tdmweb.publish.iterationsBeforeCommit=20000
repeater
performance based on number of rows
800,000
35 seconds
10,000,000
6 min 33 seconds
100,000,000
1 hour 6 min 46 seconds
SQL Server Target publish (iterationsBeforeCommit=50000)
tdmweb.publish.batchCommit=true
tdmweb.publish.iterationsBeforeCommit=
5
0000
repeater
performance based on number of rows
1,000,000
42 seconds
10,000,000
6 min 31 seconds
Test 3 - With Expressions
We publish using several expressions.
Table DDL Used
CREATE TABLE equifax_records ( "update_period" numeric (38, 0) , "peer" varchar (20) , "state" varchar (20) , "county" varchar (20) , "product" varchar (20) , "vintage" varchar (20) , "originalrisk" varchar (20) , "currentrisk" varchar (20) , "term" varchar (20) , "smallbusinessownerflag" varchar (20) , "mortgageindicator" varchar (20) , "consumer_age" varchar(20) , "edti" varchar (20) , "pim" varchar (20) , "n_cur" numeric (38, 0) , "n_030" numeric (38, 0) , "n_060" numeric (38, 0) , "n_090" numeric (38, 0) , "n_120" numeric (38, 0) , "n_svr" numeric (38, 0) , "n_bkr" numeric (38, 0) , "n_misc" numeric (38, 0) , "n_closed_pos" numeric (38, 0) , "bal_cur" numeric (38, 0) , "bal_030" numeric (38, 0) , "bal_060" numeric (38, 0) , "bal_090" numeric (38, 0) , "bal_120" numeric (38, 0) , "bal_svr" numeric (38, 0) , "bal_misc" numeric (38, 0) , "bal_closed_pos" numeric (38, 0) , "pmt" numeric (38, 0) , "hc" numeric (38, 0) , "bal_bk" numeric (38, 0) , "n_fcs" numeric (38, 0) , "bal_fcs" numeric (38, 0) , "n_pos_bal" numeric (38, 0) );
Expressions Used
Table Name
Column Name
Definition
Data Type
EQUIFAX_RECORDS
n_pos_bal
@randlov(0,@perclist(90%@randrange(1,2)@,5%0,5%@randrange(3,10)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
vintage
[email protected](0,@list(1,2,3,4)@)@@string(@randdate(2005/01/01,~YEAR~/01/01)@,YYYY)@
VARCHAR(20)
EQUIFAX_RECORDS
peer
@randlov(0,@list(GM,CM,CP,NC,OT)@)@
VARCHAR(20)
EQUIFAX_RECORDS
bal_closed_pos
@if(^bal_cur^=0,@randrange(0,60000)@,0)@
NUMBER(38)
EQUIFAX_RECORDS
n_bkr
@randlov(0,@perclist(1%1,99%0)@)@
NUMBER(38)
EQUIFAX_RECORDS
state
@seqlov(0,@seedlist(State_County,S)@,2)@
VARCHAR(20)
EQUIFAX_RECORDS
bal_120
@randlov(0,@perclist(99%0,1%@randrange(0,30000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
n_120
@randlov(0,@perclist(1%1,99%0)@)@
NUMBER(38)
EQUIFAX_RECORDS
edti
@randrange(0,9)@
VARCHAR(20)
EQUIFAX_RECORDS
n_svr
@randlov(0,@perclist(1%1,99%0)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_060
@randlov(0,@perclist(99%0,1%@randrange(0,60000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
hc
@if(^bal_cur^=0,0,@addrand(^bal_cur^,0,20000)@)@
NUMBER(38)
EQUIFAX_RECORDS
update_period
[email protected](0,@list(01,02,03,04,05,06,07,08,09,10,11,12)@)@
NUMBER(38)
EQUIFAX_RECORDS
originalrisk
@randrange(0,14)@
VARCHAR(20)
EQUIFAX_RECORDS
smallbusinessownerflag
@randlov(0,@perclist(15%1,85%~EMPTY~)@)@
VARCHAR(20)
EQUIFAX_RECORDS
n_misc
@randlov(0,@perclist(1%1,98%0,1%-1)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_030
@randlov(0,@perclist(97%0,3%@randrange(0,90000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_cur
@randlov(0,@perclist(20%@randrange(100000,900000)@,40%@randrange(0,9000)@,35%@randrange(10000,90000)@,5%@randrange(1000000,2000000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_fcs
0
NUMBER(38)
EQUIFAX_RECORDS
bal_misc
@randlov(0,@perclist(99%0,1%@randrange(0,20000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
n_060
@randlov(0,@perclist(25%1,70%0,5%2)@)@
NUMBER(38)
EQUIFAX_RECORDS
n_090
@randlov(0,@perclist(1%1,99%0)@)@
NUMBER(38)
EQUIFAX_RECORDS
n_030
@randlov(0,@perclist(45%1,35%0,15%2,1%3,1%4,1%5,1%10,1%25)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_bk
0
NUMBER(38)
EQUIFAX_RECORDS
pmt
@randlov(0,@perclist(80%@randrange(0,1000)@,20%@randrange(1000,10000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
county
@upper(@seqlov(0,@seedlist(State_County,S)@,5)@)@
VARCHAR(20)
EQUIFAX_RECORDS
pim
@randrange(0,9)@
VARCHAR(20)
EQUIFAX_RECORDS
n_fcs
0
NUMBER(38)
EQUIFAX_RECORDS
mortgageindicator
@randlov(0,@perclist(45%1,55%0)@)@
VARCHAR(20)
EQUIFAX_RECORDS
n_cur
@randrange(1,6)@
NUMBER(38)
EQUIFAX_RECORDS
term
@randrange(0,11)@
VARCHAR(20)
EQUIFAX_RECORDS
n_closed_pos
@randlov(0,@perclist(10%1,89%0,1%2)@)@
NUMBER(38)
EQUIFAX_RECORDS
bal_090
@randlov(0,@perclist(99%0,1%@randrange(0,60000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
currentrisk
@randrange(0,14)@
VARCHAR(20)
EQUIFAX_RECORDS
product
@randlov(0,@list(AB1,AF2,AF1,AB2)@)@
VARCHAR(20)
EQUIFAX_RECORDS
bal_svr
@randlov(0,@perclist(99%0,1%@randrange(0,60000)@)@)@
NUMBER(38)
EQUIFAX_RECORDS
consumer_age
@randrange(0,7)@
VARCHAR(20)
Publish to CSV
repeater
performance based on number of rows
100,000
14 min 51 seconds