ETLBenchmarks Manapps 090203 - Dataprix€¦ · Test2 0 0 0 0 0 Test3 13 3 7 9 11 Test4 8 7 12 5 13...

MANAPPS

MANAPPS

o

o

o

MANAPPS

o

o

TOS 2.4.1 PDI 3.0.0 IBM DS 7.5 IBM DS PX 7.5 INFA PWC 8.1.1 Test1 13 7 19 8 16 Test2 0 0 0 0 0 Test3 13 3 7 9 11 Test4 8 7 12 5 13 Test5 15 4 13 12 18 Test6 15 4 10 5 12 Test7 11 3 7 8 15 Test8 13 12 5 14 16 Test8.2 12 13 4 15 18 Test8.3 12 12 4 15 17 Test9 12 6 15 12 17 Test9.2 16 5 12 9 19 Test9.3 12 8 13 11 16 Test10 20 7 12 10 13 Test10.2 20 6 6 13 16 Test10.3 16 6 6 14 18 Test10.4 12 4 8 17 19 Test11 20 7 10 8 16 Test11.2 20 6 6 12 16 Test11.3 16 6 6 13 19 Test12 20 8 13 6 13 Test12.2 20 7 6 11 16 Test12.3 17 7 5 12 19

Total 333 148 199 239 353

MANAPPS

MANAPPS

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,00 7,80 39,10 162,09

PDI 3.0.0 2,00 15,50 83,80 417,80

IBM DS 7.5 2,00 4,00 12,50 66,00

IBM DS PX 7.5 3,40 12,00 40,00 150,00

INFA PWC 8.1.1 2,00 7,00 18,00 74,00

MANAPPS

MANAPPS

Lines 100 000 1 000 000 5 000 000

TOS 2.4.1 15,26 144,50 731,78

PDI 3.0.0 14,90 151,80 843,90

TOS 2.4.1 with Extended Insert 2,60 25,00 129,00

MANAPPS

MANAPPS

Lines 100 000 500 000 1 000 000

TOS 2.4.1 2,25 6,26 14,25

PDI 3.0.0 4,78 21,20 37,40

IBM DS 7.5 4,00 11,00 19,00

IBM DS PX 7.5 4,00 8,00 15,00

INFA PWC 8.1.1 5 6 9

MANAPPS

MANAPPS

Lines 100 000 1 000 000 2 000 000

TOS 2.4.1 4,36 22,12 49,66

PDI 3.0.0 2,60 30,60 72,70

IBM DS 7.5 3,00 18,00 40,00

IBM DS PX 7.5 6,00 27,00 55,00

INFA PWC 8.1.1 4 7 11

MANAPPS

• • •

MANAPPS

MANAPPS

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,30 8,50 43,10 183,13

PDI 3.0.0 5,30 51,00 259,40 1126,10

IBM DS 7.5 2,00 10,00 56,00 178,00

IBM DS PX 7.5 4,75 11,33 41,00 155,00

INFA PWC 8.1.1 3,00 6,00 17,00 74,00

MANAPPS

MANAPPS

Lines 100 000 500 000 1 000 000

TOS 2.4.1 1,24 1,4 1,69

PDI 3.0.0 4,26 22,26 47,80

IBM DS 7.5 2,40 8,00 13,67

IBM DS PX 7.5 8,00 12,00 17,50

INFA PWC 8.1.1 4 3 4

MANAPPS

MANAPPS

Lines 100 000 500 000 1 000 000

TOS 2.4.1 5,99 23,26 52,72

PDI 3.0.0 38,35 201,60 382,60

IBM DS 7.5 12,70 65,00 116,00

IBM DS PX 7.5 15,00 30,50 47,50

INFA PWC 8.1.1 5 9 14

MANAPPS

• • •

MANAPPS

Sorted by age

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,44 15,73 188,21 1016,03

PDI 3.0.0 3,63 32,85 155,95 668,20

IBM DS 7.5 4,20 60,70 267,70

IBM DS PX 7.5 4,00 16,25 64,50 492,67

INFA PWC 8.1.1 5,00 13,00 50,00 201,00

MANAPPS

Sorted by firstname

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,69 18,05 168,46 1071,20

PDI 3.0.0 3,40 31,20 157,15 739,20

IBM DS 7.5 6,00 58,00 426,00

IBM DS PX 7.5 4,00 16,00 57,00 624,00

INFA PWC 8.1.1 4,00 13,00 51,00 223,00

MANAPPS

Sorted by age & firstname

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,33 17,40 225,03 1007,00

PDI 3.0.0 3,22 29,27 159,10 842,20

IBM DS 7.5 7,33 60,00 360,00

IBM DS PX 7.5 4,50 16,33 59,00 582,50

INFA PWC 8.1.1 5,00 13,00 49,00 211,00

MANAPPS

MANAPPS

• •

•

MANAPPS

MANAPPS

• •

•

MANAPPS

Group by Age (Count)

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 0,62 6,99 30,05 124,16

PDI 3.0.0 2,70 26,53 134,30 466,50

IBM DS 7.5 2,00 6,00 21,00 128,00

IBM DS PX 7.5 4,00 6,50 21,33 78,00

INFA PWC 8.1.1 3,00 5,00 8,00 27,00

MANAPPS

Group by Age (Count, Sum(Rate), Avg(Rate), Min(Rate), Max(Rate))

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 0,84 7,44 37,61 139,12

PDI 3.0.0 2,60 25,20 138,30 426,00

IBM DS 7.5 2,00 11,00 50,00 184,00

IBM DS PX 7.5 11,25 15,33 33,50 254,33

INFA PWC 8.1.1 2,00 6,00 12,00 38,00

MANAPPS

Group by FirstName (Count)

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 0,86 7,89 198,79 928,08

PDI 3.0.0 2,70 29,70 162,30 544,00

IBM DS 7.5 2,00 14,00 68,00 424,00

IBM DS PX 7.5 4,50 11,00 40,00 505,00

INFA PWC 8.1.1 4 9 23 85

MANAPPS

MANAPPS

Lookup 100 000 rows ~7MB

Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,45 6,39 28,72 108,37

PDI 3.0.0 4,14 21,40 87,60 288,90

IBM DS 7.5 5,00 10,60 33,00 139,00

IBM DS PX 7.5 5,00 12,20 40,00 122,00

INFA PWC 8.1.1 5,00 11,00 32,00 116,00

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 3,9 8,89 32,36 115,67

PDI 3.0.0 7,90 24,50 97,40 291,10

IBM DS 7.5 28,00 33,00 56,00 195,00

IBM DS PX 7.5 7,00 13,00 40,00 122,00

INFA PWC 8.1.1 4,00 11,00 33,00 122,00

MANAPPS

Lookup 1 000 000 rows ~68MB


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 9,86 14,26 38,6 121,44

PDI 3.0.0 14,50 32,20 116,60 487,25

IBM DS 7.5 68,30 80,00 102,00 203,00

IBM DS PX 7.5 9,25 15,00 40,00 123,00

INFA PWC 8.1.1 5,00 12,00 35,00 142,00

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 56,51 69,1 199,26 557,1

PDI 3.0.0

IBM DS 7.5 369,00 407,00 496,00 973,00

IBM DS PX 7.5 24,00 30,00 55,00 134,00

INFA PWC 8.1.1 11,00 14,00 42,00 141,00

MANAPPS

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,51 6,74 29,55 101,65

PDI 3.0.0 3,30 17,10 78,40 305,00

IBM DS 7.5 6,00 10,50 36,00 144,00

IBM DS PX 7.5 7,00 14,00 41,00 137,00

INFA PWC 8.1.1 5,00 10,00 33,00 120,00

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 4,26 9,28 32,44 111,98

PDI 3.0.0 7,80 20,50 81,50 310,00

IBM DS 7.5 28,60 34,00 57,00 173,00

IBM DS PX 7.5 7,50 14,25 44,67 155,20

INFA PWC 8.1.1 5,00 10,00 34,00 126,00

MANAPPS

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 10,2 15,22 38,31 126,63

PDI 3.0.0 14,10 32,35 111,35 319,05

IBM DS 7.5 66,00 68,00 95,00 220,00

IBM DS PX 7.5 9,00 18,00 51,00 153,33

INFA PWC 8.1.1 6,00 14,00 34,00 130,00

MANAPPS

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 1,42 5,65 24,63 106,78

PDI 3.0.0 2,60 13,00 59,80 327,60

IBM DS 7.5 6,00 10,00 30,00 137,00

IBM DS PX 7.5 9,00 15,25 47,33 146,00

INFA PWC 8.1.1 4,00 12,00 33,00 121,00

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 4,16 8,74 30,34 120,53

PDI 3.0.0 7,26 19,30 72,25 319,60

IBM DS 7.5 28,00 35,50 63,00 189,50

IBM DS PX 7.5 11,00 16,00 44,00 150,00

INFA PWC 8.1.1 5 11 33 127

MANAPPS


Lines 100 000 1 000 000 5 000 000 20 000 000

TOS 2.4.1 10,98 15,18 38,49 126,57

PDI 3.0.0 13,30 27,35 79,00 413,45

IBM DS 7.5 38,49 90,40 108,00 231,00

IBM DS PX 7.5 13,00 19,00 49,00 134,00

INFA PWC 8.1.1 6 13 37 131

MANAPPS

Test 1: File Input Delimited > File Output Delimited

- dynamic partitioning at 2 with more than 5 millions rows

This is a Disk Bounded test

Test 2: File Input Delimited > Table MySQL Output

Not Applicable

Test 3: Table Oracle Input > File Output Delimited

- no partitioning as it's too small in volume and short in time

Test 4: File Input Delimited > Table Output Oracle BULK

MANAPPS

- commit size at 100000

- dynamic partitioning at 2 with 2 millions rows


Test 5: File Input Delimited > Transform > File Output Delimited

- function "CONCAT(CONCAT(firstname,' '),lastname)" is replaced by "firstname || ' ' || lastname"

- dynamic partitioning at 2 with more than 5 millions rows


Test 6: Table Input Oracle > Aggregation > Table Output Oracle (ELT)


Oracle database is not 'tuned' for ELT mode

Test 7: Tables Input Oracle > Transformation > Tables Output Oracle (ELT)

- commit size at 50000


Oracle database is not 'tuned' for ELT mode

Test 8: File Input Delimited > Sort > File Output Delimited

- sorter memory adjustment

This is a memory limited test at 20 millions rows (2 pass sort are required) and also disk limited sometime

Test 9: File Input Delimited > Aggregate > File Output Delimited

- dynamic partitioning at 2 with more than 5 millions rows in source

- aggregator memory adjustment

This is a CPU bounded test

Test 10: File Input Delimited > Lookup > File Output Delimited

- dynamic partitioning at 2 with more than 5 millions rows in source or lookup

- lookup memory adjustment

- lookup in the flow with hash partitioning point


Test 11: File Input Delimited > Lookup > File Output Delimited && rejects

- use of router in place of filters





Test 12: file_input_delimited >_file_lookup_delimited > file_output_delimited__rejects && innerjoin_rejects_file_output_delimited

- use of router in place of filters





ETLBenchmarks Manapps 090203 - Dataprix€¦ · Test2 0 0 0 0 0 Test3 13 3 7 9 11 Test4 8 7 12 5 13...

Documents

Transcript of ETLBenchmarks Manapps 090203 - Dataprix€¦ · Test2 0 0 0 0 0 Test3 13 3 7 9 11 Test4 8 7 12 5 13...