Copying many large formulas (neural nets) between data tables is very, very slow

jwalk

I have a dataset of 10000 rows x 100+ columns used for neural network modeling. I have made 75 different models of 20 nodes x 100 inputs/node and saved the formulas using fast formulas. I have 10 different tables (same dataset, different modeling), each with 75 models and I want to merge all of the models (formulas) into one master table, since I want to apply all of the models to any new data I collect. 

 

Every method I try takes forever. I have used the Copy Formulas Add-in to copy each table's formulas to a single table and it takes about 2 days total to complete all of the table merges and with no indication of progress, it's hard to know if it is still working or has frozen.

 

I have used Join by matching rows with suppress formula evaluation to get them all into one table and this is fast. However, when I turn formula evaluation back on by manually selecting the formula columns and using Columns>Standardize Attributes>Column Properties>Formula>Suppress Eval, the recalculation of the 750 formulas takes forever. It has been running for 30 hours (maxed out at 12% processor utilization) and I have no idea what the progress is.

 

I have tried looping through the columns one at a time to turn formula evaluation on but even doing this for 1 column took over 5 minutes, which seems incredibly long. 

 

Is there any way to speed this up? I know there are workarounds but this seems like it should be much easier and faster than this.

 

ian_jmp

With 'larger' problems it's hard to offer general advice about efficiency without knowing some or all of the details. If you haven't already, you could look at the impact of the 'Begin Data Update()' and 'End Data Update()' table messages, and/or controlling the way formulas are evaluated (or not). FWIW, my instinct tells me that you may do better by using the Formula Depot as an intermediary. I started to write some code to check this out but for problems of this size the modelling itself takes some time so I didn't continue down this path:

NamesDefaultToHere(1);

// Define the data volume
nr = 100;
nc = 10;

// Make some random data
dt = AsTable(J(nr, nc, RandomNormal(0, 1)));
dt << setName("Random Data");

// Fit neural and publish prediction formula
cols = dt << getColumnNames("String");
yCol = cols[NItems(cols)];
RemoveFrom(cols, NItems(cols));
nn = dt << Neural(Y(Eval(yCol)), X(Eval(cols)), Fit( NTanH( 3 ) ), Invisible);
nn(Fit(1)) << publishPredictionFormula;
Report(nn) << closeWindow;