Update Table Match Speed - which is faster: Matching multiple columns X, Y, Z (all integers) or Matching Col "X.Y.Z" unique combined string?

MeanChris

I'm Updating a large data table (4.4 million rows) of stacked data with design info from a much smaller table.  I have 1,927 unique rows in the smaller table that correspond to the stacked parameter.  They can be identified either by a column with a unique string (X.Y.Z), or the combination of three columns that contain X, Y and Z individually.  The same columns (X, Y, Z and X.Y.Z) are in the large data table.  X, Y and Z are all integers.

 

I'm not sure how to test this, but I'm curious if matching 3 integer columns is faster than matching 1 string column by any appreciable amount.  May need to repeat this fairly often, and the large data table is still growing.

mmarchandFSLR

I created some random data to test this on 1 million rows.  Updating by matching three integer columns was on average twice as fast as on the single character column.

 

Names Default to Here( 1 );
dt = New Table( "Match Test", New Column( "X", numeric ), New Column( "Y", numeric ), New Column( "Z", numeric ), New Column( "XYZ", character ), New Column( "Update", character ) );
dt << Add Rows( 1e6 );
For Each Row( dt, :X = Random Integer( 999 ); :Y = Random Integer( 999 ); :Z = Random Integer( 999 ); :XYZ = Char( :X ) || " " || Char(:Y ) || " " || Char( :Z );  );
dt2 = New Table( "Match Test", New Column( "X", numeric ), New Column( "Y", numeric ), New Column( "Z", numeric ), New Column( "XYZ", character ), New Column( "Update", character ) );
dt2 << Add Rows( 1e6 );
For Each Row( dt2, :X = Random Integer( 999 ); :Y = Random Integer( 999 ); :Z = Random Integer( 999 ); :XYZ = Char( :X ) || " " || Char(:Y ) || " " || Char( :Z ); :Update = {"taco", "cat", "goat", "cheese", "pizza"}[Random Integer( 5 )] );
For Each( {v, i}, 1::10, 
start_time = HP Time();
dt << Update( With( dt2 ), Match Columns( :XYZ = :XYZ ) );
char_time = HP Time() - start_time;
Print( "Update on single character column match: " || Char( char_time / 1e6 ) || " seconds" );
start_time = HP Time();
dt << Update( With( dt2 ), Match Columns( :X = :X, :Y = :Y, :Z = :Z ) );
num_time = HP Time() - start_time;
Print( "Update on three integer columns match: " || Char( num_time / 1e6 ) || " seconds" ) );

//"Update on single character column match: 1.700707 seconds"
//"Update on three integer columns match: 0.70421 seconds"
//"Update on single character column match: 1.508058 seconds"
//"Update on three integer columns match: 0.718748 seconds"
//"Update on single character column match: 1.316761 seconds"
//"Update on three integer columns match: 0.654922 seconds"
//"Update on single character column match: 1.344123 seconds"
//"Update on three integer columns match: 0.659323 seconds"
//"Update on single character column match: 1.284766 seconds"
//"Update on three integer columns match: 0.654989 seconds"
//"Update on single character column match: 1.272866 seconds"
//"Update on three integer columns match: 0.666974 seconds"
//"Update on single character column match: 1.289576 seconds"
//"Update on three integer columns match: 0.656926 seconds"
//"Update on single character column match: 1.276214 seconds"
//"Update on three integer columns match: 0.707188 seconds"
//"Update on single character column match: 1.689671 seconds"
//"Update on three integer columns match: 0.668045 seconds"
//"Update on single character column match: 1.375964 seconds"
//"Update on three integer columns match: 0.675418 seconds"