JMP > JSL > Script Acceleration for Multiple Call to Fit Model (N > 2000)?

[#1]

Hi JMP Community,

The size of my datasets is getting bigger and the basic scripts I created a while back (see below) are not keeping up from a speed point of view.

I would greatly appreciate hearing about ideas on how to optimize this type of script for better speed. For example, I suspect that operations like "Concatenate" are relatively slow, and it might make sense to work with matrices for this type of operation.

Names Default to Here (1);

dt = Current Data Table ();

CList = dt << Get Column Names (string);

dtoutl = New Table ("LSM OUTPUT", "invisible");
dtoutp = New Table ("PVal OUTPUT", "invisible");


For (i = 1, i<= 2000, i++,  

	fm = dt <<	Fit Model(
					Y( Column (i) ),
					Effects( :VISIT, :TREATMENT, :VISIT * :TREATMENT, Column (i+2000) ),
					Personality( "Standard Least Squares" ),
					Emphasis( "Minimal Report" ),
					Run(
						Column (i) << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
						Parameter Estimates( 1 ), Scaled Estimates( 0 ),
						Plot Actual by Predicted( 0 ), Plot Regression( 0 ),
						Plot Residual by Predicted( 0 ), Plot Studentized Residuals( 0 ),
						Plot Effect Leverage( 0 ), Plot Residual by Normal Quantiles( 0 ),
						Box Cox Y Transformation( 0 ), {:VISIT * :TRT01A <<
						{LSMeans Contrast( [0 0 1 -1 0 0, 0 0 0 0 1 -1] )}}}
					)
					
				);
	fmr = report (fm);
	
	table_list = fmr << xpath ("//OutlineBox [text() = 'VISIT*TREATMENT']//TableBox");
	
	temp_lsm = table_list [1];
	temp_pval = table_list [2];
	
	temp_tbl_lsm = temp_lsm << Make into Data Table (invisible (1));
	temp_tbl_pval = temp_pval << Make into Data Table (invisible (1));
	
	temp_tbl_lsm << New column ("ID", character, nominal, << set each value (CList [i]));
	temp_tbl_pval << New column ("ID", character, nominal, << set each value (CList [i]));
	
	fmr << close window;
	
	dtoutl << Concatenate (Data table (temp_tbl_lsm), "Append to First Table");
	Wait (0);
	dtoutp << Concatenate (Data table (temp_tbl_pval), "Append to First Table");
	
	Close (temp_tbl_lsm, NoSave);	
	Close (temp_tbl_pval, NoSave);	
		
	
);


dtoutl << Bring Window to Front;
dtoutp << Bring Window to Front;

All feedback is welcome.

Best,

TS

[#2 · Accepted solution]

Hi JMP Community,

I have experimented with matrices as receptacles for aggregating long collections of analysis output tables, and so far I have accelerated my execution time by ~5 folds (see below). The only minor inconvenience is that I have had to rebuild the annotations for the captured output tables because (obviously) matrices do not hold text.

Names Default to Here (1);

st = Today();

dt = Current Data Table ();
dts = dt << Summary( Group( :VISIT, :TRT01A ), Freq( "None" ), Weight( "None" ), invisible );
annot_lsm = {};
met_l = {};
annot_pv = {};

CList = dt << Get Column Names (string);

For (j = 1, j <= N Rows (dts), j++,
	insert into (annot_lsm, dts:VISIT [j] || ", " || dts:TRT01A [j]);
);

annot_pv = annot_lsm;

annot_pv = Concat To(annot_pv, {"Estimate","Std Error", "t Ratio", "Prob>|t|", "SS", "Lower 95%", "Upper 95%"});

Close (dts, NoSave);

mtx_lsm = [];
mtx_pval = [];

For (i = 1, i<= 2000, i++,  //2000

	fm = dt <<	Fit Model(
					Y( Column (i) ),
					Effects( :VISIT, :TRT01A, :VISIT * :TRT01A, Column (i+2000) ),
					Personality( "Standard Least Squares" ),
					Emphasis( "Minimal Report" ),
					Run(
						Column (i) << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
						Parameter Estimates( 1 ), Scaled Estimates( 0 ),
						Plot Actual by Predicted( 0 ), Plot Regression( 0 ),
						Plot Residual by Predicted( 0 ), Plot Studentized Residuals( 0 ),
						Plot Effect Leverage( 0 ), Plot Residual by Normal Quantiles( 0 ),
						Box Cox Y Transformation( 0 ), {:VISIT * :TRT01A <<
						{LSMeans Contrast( [0 0 1 -1 0 0, 0 0 0 0 1 -1] )}}}
					)
					
				);
	
	fmr = report (fm);
	
	insert into (met_l, CList [i]);
	
	table_list = fmr << xpath ("//OutlineBox [text() = 'VISIT*TRT01A']//TableBox");
	
	temp_lsm = table_list [1];
	temp_pval = table_list [2];
	
	temp_mtx_lsm = temp_lsm << Get as Matrix;
	temp_mtx_pval = temp_pval << Get as Matrix;
	
	fmr << close window;
	
	mtx_lsm = mtx_lsm |/ temp_mtx_lsm;
	mtx_pval = mtx_pval |/ temp_mtx_pval ;
		
);
dtoutl = as Table (mtx_lsm);
dtoutp = as Table (mtx_pval);

dtoutl << new column ("COMP", Character, Nominal);
dtoutl << new column ("MET_ID", Character, Nominal);
dtoutl:Col1 << set name ("LSMEAN");
dtoutl:Col2 << set name ("STDERR");
dtoutl:Col3 << set name ("UPPER CI");
dtoutl:Col4 << set name ("LOWER CI");

dtoutl << Begin Data Update;
	For Each Row( dtoutl, :COMP = annot_lsm[Sequence( 1, N Items (annot_lsm), 1, 1 )] );
	For each row( dtoutl, :MET_ID = met_l[Sequence (1,N Items (met_l), 1, 6)] );
dtoutl << End Data Update;

dtoutp << new column ("LABEL", Character, Nominal);
dtoutp << new column ("MET_ID", Character, Nominal);

dtoutp << Begin Data Update;
	For Each Row( dtoutp, :LABEL = annot_pv[Sequence( 1, N Items (annot_pv), 1, 1 )] );
	For each row( dtoutp, :MET_ID = met_l[Sequence (1,N items (met_l), 1, 13)] );
dtoutp << End Data Update;

[#3 · Accepted solution]

5X sounds great!

There might also be an invisible option for the platform that might help.

(Try the JSL profiler, the next suggestion may be way off-base. There is no point making something faster that uses < 1% of the time...)

If it still isn't enough, the last two statements in the loop may be the next place to look. I think your JSL is reallocating the arrays 2000 times at progressively larger sizes. If you can pre-allocate the arrays (using the J(nr,nc,initvalue) function) and store into them with something like mtx_lsm[a::b,0] = temp_mtx_lsm[0,0] you'll save some time.

Edit: this is potentially an N^2 problem because it not only reallocates, it copies the data too; going from a run of 1000 to a run of 2000 will copy 4X (not 2X) the amount of data.

[#4]

Hi @Craige_Hales ,

Thanks for the suggestion. Thanks to the method you recommended, I have improved the speed by another 33%.

There are still some tweaks that I need to perfect but I started at a total run time of about 450 seconds, down to 91 seconds with my initial changes, and down to 61 seconds now. 

Best

TS

[#5]

Cool! I think the community would like to see what you did to get there, or maybe summarize how important different changes were.

@DonMcCormack 

[#6]

Hi JMP Community,

Find below the annotated version of the most improved script to run and capture the output of a large number of Fit Model analyses.

The two most essential steps to improve the run time are:

1. Usage of matrices instead of Data Tables to aggregate the analysis output
2. Replacement instead of insertion of the analysis output data into master matrices created ahead of the main loop.

Names Default to Here (1);

st = Today();

dt = Current Data Table ();

lc_path = dt << GetPath;

// START: CAPTURING OUTPUT ANNOTATION TO REBUILD ANNOTATION // 
dts = dt << Summary( Group( :VISIT, :TRT01A ), Freq( "None" ), Weight( "None" ), invisible );
annot_lsm = {};
met_l = {};
annot_pv = {};

CList = dt << Get Column Names (string);

For (j = 1, j <= N Rows (dts), j++,
	insert into (annot_lsm, dts:VISIT [j] || ", " || dts:TRT01A [j]);
);

annot_pv = annot_lsm;

annot_pv = Concat To(annot_pv, {"Estimate","Std Error", "t Ratio", "Prob>|t|", "SS", "Lower 95%", "Upper 95%"});

Close (dts, NoSave);
// END: CAPTURING OUTPUT ANNOTATION TO REBUILD ANNOTATION // 

// START: CREATING EMPTY NASTER MATRICES TO HOLD TTHE OUTPUT OF THE FIT MODEL//
nro_l = 1933*6;
nco_l = 4;

nro_p = 1933 * 13;
nco_p = 2;

mtx_lsm = J(nro_l, nco_l);
mtx_pval = J(nro_p, nco_p);
// END: CREATING EMPTY MASTER MATRICES TO HOLD TTHE OUTPUT OF THE FIT MODEL//

//START MAIN LOOP//
For (i = 3921, i<= 5853, i++,  //5853
	
	//START LINEAR MODEL WITH CUSTGOMIZED CONTRASTS //
	
	fm = dt <<	Fit Model(
					Y( Column (i) ),
					Effects( :VISIT, :TRT01A, :VISIT * :TRT01A, Column (i-1933) ),
					Personality( "Standard Least Squares" ),
					Emphasis( "Minimal Report" ),
					Run(
						Column (i) << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
						Parameter Estimates( 1 ), Scaled Estimates( 0 ),
						Plot Actual by Predicted( 0 ), Plot Regression( 0 ),
						Plot Residual by Predicted( 0 ), Plot Studentized Residuals( 0 ),
						Plot Effect Leverage( 0 ), Plot Residual by Normal Quantiles( 0 ),
						Box Cox Y Transformation( 0 ), {:VISIT * :TRT01A <<
						{LSMeans Contrast( [0 0 1 -1 0 0, 0 0 0 0 1 -1] )}}}
					)
					
				);
	//END LINEAR MODEL WITH CUSTGOMIZED CONTRASTS //
	
	//START CAPTURE ANALYSIS OUTPUTS FOR EACH RUN//
	fmr = report (fm);
	
	insert into (met_l, CList [i]); //COLLATING THE NAME OF THE COLUMN FOR EACH RUN//
	
	table_list = fmr << xpath ("//OutlineBox [text() = 'VISIT*TRT01A']//TableBox");
	
	temp_lsm = table_list [1]; //POINTER TO LSMEAN + STDERR TABLE//
	temp_pval = table_list [2]; // POINTER TO CONTRAST TABLE //
	
	temp_mtx_lsm = temp_lsm << Get as Matrix; // EXTRACT LSMEAN + STDERR OUTPUT AS A MATRIX //
	temp_mtx_pval = temp_pval << Get as Matrix; // EXTRACT CONTRAST OUTPUT AS A MATRIX //
	
	locr1_l = ((i-3921)*6) + 1; //GENERATE THE TOP ROW BLOCK FOR INSERTION OF LSMEAN + STDERR IN TO THE MASTER MATRIX //
	locr2_l = ((i-3921)*6) + 6; //GENERATE THE BOTTOM ROW BLOCK FOR INSERTION OF LSMEAN + STDERR IN TO THE MASTER MATRIX //
	
	mtx_lsm [locr1_l::locr2_l, 0] = temp_mtx_lsm [0,0]; //REPLACE PLACEHOLDER VALUES IN MASTER MATRIX WITH LOCAL LSMEAN + STDERR OUTPUT DATA FROM EACH RUN //
	
	locr1_p = ((i-3921)*13) + 1; //GENERATE THE TOP ROW BLOCK FOR INSERTION OF CONTRAST IN TO THE MASTER MATRIX //
	locr2_p = ((i-3921)*13) + 13; //GENERATE THE BOTTOM ROW BLOCK FOR INSERTION OF CONTRAST IN TO THE MASTER MATRIX //
	
	mtx_pval [locr1_p::locr2_p, 0] = temp_mtx_pval [0,0]; //REPLACE PLACEHOLDER VALUES IN MASTER MATRIX WITH LOCAL CONTRAST OUTPUT DATA FROM EACH RUN //
	
	fmr << close window;	
);
// START CONVERT MASTER MATRICES INTO TABLES//
dtoutl = as Table (mtx_lsm); 
dtoutp = as Table (mtx_pval);
// END CONVERT MASTER MATRICES INTO TABLES//

//START REBUILD ANNOTATIONS AND REFORMAT FOR LSMEAN + STDERR TABLE//
dtoutl << new column ("COMP", Character, Nominal);
dtoutl << new column ("MET_ID", Character, Nominal);
dtoutl:Col1 << set name ("LSMEAN");
dtoutl:Col2 << set name ("STDERR");
dtoutl:Col3 << set name ("UPPER CI");
dtoutl:Col4 << set name ("LOWER CI");

dtoutl << Begin Data Update;
	For Each Row( dtoutl, :COMP = annot_lsm[Sequence( 1, N Items (annot_lsm), 1, 1 )] );
	For each row( dtoutl, :MET_ID = met_l[Sequence (1,N Items (met_l), 1, 6)] );
dtoutl << End Data Update;

dtoutl << New Column ("METAB_ID", Character, Nominal, Formula (Substitute (:MET_ID, "LOG10 FC ", ""))) ;
dtoutl << New Column ("VISIT", Character, Nominal, Formula (Word (1,:COMP, ",")));
dtoutl << New Column ("ARM", Character, Ordinal, Formula (Word (-1, :COMP, ",")));

dtoutl:METAB_ID << Delete Formula;
dtoutl:ARM << Delete Formula;
dtoutl:VISIT << Delete Formula;
//END REBUILD ANNOTATIONS AND REFORMAT FOR LSMEAN + STDERR TABLE//

//START REBUILD ANNOTATIONS AND REFORMAT FOR CONTRAST TABLE//
dtoutp << new column ("LABEL", Character, Nominal);
dtoutp << new column ("MET_ID", Character, Nominal);

dtoutp << Begin Data Update;
	For Each Row( dtoutp, :LABEL = annot_pv[Sequence( 1, N Items (annot_pv), 1, 1 )] );
	For each row( dtoutp, :MET_ID = met_l[Sequence (1,N items (met_l), 1, 13)] );
dtoutp << End Data Update;

CNAME1 = Column (dtoutp, 3) [3];
CNAME2 = Column (dtoutp, 3) [5];

dtoutp << Select Where (:LABEL == "Prob>|t|");

dtoutp_s = dtoutp << subset (Selected Rows, Output Table Name ("P VAL"));

Close (dtoutp, NoSave);

dtoutp_s << New Column ("METAB_ID", Character, Nominal, Formula (Substitute (:MET_ID, "LOG10 FC ", "")));

dtoutp_s:Col1 << Set Name (CNAME1);
dtoutp_s:Col2 << Set Name (CNAME2);

dtoutp_s_s = dtoutp_s << Stack (Columns (as name(CNAME1), as name (CNAME2)), Output Table Name ("STACK P VAL"));

Close (dtoutp_s, NoSave);

dtoutp_s_s << New Column ("VISIT", Character, Nominal, Formula (Word (1,:Label, ",")));
dtoutp_s_s << New Column ("xARM", Character, Ordinal, Formula (Word (-1, :Label, ",")));
dtoutp_s_s:Data << Set name ("NOM P VAL");

dtoutl << Update(
	With( dtoutp_s_s ),
	Match Columns( :METAB_ID = :METAB_ID, :VISIT = :VISIT ),
	Add Columns from Update Table( :NOM P VAL ),
	Replace Columns in Main Table( None )
);

Close (dtoutp_s_s, NoSave);
//END REBUILD ANNOTATIONS AND REFORMAT FOR CONTRAST TABLE//
dtoutl << Save (substitute (lc_path, dt << get name||".jmp", "LINMOD_OUT_")||char(today())); //

et = Today ();

show (Date Difference (st, et, "second"));

 Best,

TS

[#7 · Accepted solution]

These might give some further (most likely fairly small) speed increases:

You can most likely add invisible to fit model

fm = dt <<	Fit Model(
	Y( Column (i) ),
	Effects( :VISIT, :TRT01A, :VISIT * :TRT01A, Column (i-1933) ),
	Personality( "Standard Least Squares" ),
	Emphasis( "Minimal Report" ),
	Run(
		Column (i) << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
		Parameter Estimates( 1 ), Scaled Estimates( 0 ),
		Plot Actual by Predicted( 0 ), Plot Regression( 0 ),
		Plot Residual by Predicted( 0 ), Plot Studentized Residuals( 0 ),
		Plot Effect Leverage( 0 ), Plot Residual by Normal Quantiles( 0 ),
		Box Cox Y Transformation( 0 ), {:VISIT * :TRT01A <<
		{LSMeans Contrast( [0 0 1 -1 0 0, 0 0 0 0 1 -1] )}}}
	),
	invisible
);

Also if you are creating columns with formulas and then immediately removing them, you could use << Set Each value instead of Formula and Delete Formula

dtoutl << New Column("METAB_ID", Character, Nominal, << Set Each Value(Substitute(:MET_ID, "LOG10 FC ", "")));

You might be able to replace For Each Row with << Set Each Value also (not sure about this one, also not sure if this will have speed increase):

Column(dtoutp, "LABEL") << Set Each Value(annot_pv[Sequence( 1, N Items (annot_pv), 1, 1 )]);

You can also try to keep datatables private (at least invisible) until you need them. You can make private tables visible with << New Data View (didn't know this before today).

Close datatables / reports / windows immediately after you have no need for them.

[#8]

Hi jthi,

Making the Fit Model invisible shaved another 8 seconds (from 61 seconds to 53 seconds), and replacing the "For Each Row" with direct assignments trims another 5 seconds (53 seconds to 48 seconds).

Brilliant!

Best,

TS