topic Re: Need help with optimizing in Discussions

Need help with optimizing

Jackie_ — Thu, 10 Oct 2024 18:15:40 GMT

Hello,

I want to optimize the calculation. I tried both Col Formula and Loop but neither of them seem to be faster for rows > 10k.
I compared the exe speed and both of them take a very long time

Col Formula = 500.46s;
Loop = 491.70s;

Is there a way to optimize using Matrix or J trans?

Names Default To Here( 1 );
dt = Current Data Table();

testColumn = Column( "Test_A" );

slidervalue = 6;
////UsingFormula
start = Tick Seconds();
dt << New Column( "LL_formula",
	Numeric,
	Continuous,
	Formula(
		
						
		If( Row() < 200, // Skip the first 200 rows
			.
		,  
											
			current_device = :DeviceNumber;  // Get the current site number
			current_site = :SiteNumber; 
											// Get the indices of rows that match the current SiteNumber
			matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
           // Determine the start and end positions for the moving window
			start_row = Max( 1, Row() - 199 );
			end_row = Row();
           // Get the moving window data for matching rows
			m = Subscript( Expr( testColumn ), matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
			// Calculate the quantiles and IQR
			q1 = Quantile( 0.25, m );
			q3 = Quantile( 0.75, m );
											
           // Calculate the lower limit using the sigma function
			q1 - (q3 - q1) * (slidervalue - 0.675) / 1.35;
		)
	)
);

dt << New Column( "UL_formula",
	Numeric,
	Continuous,
	Formula(
		
		If( Row() < 200,
			.,  // Skip the first 200 rows
			
			// Get the moving window data for matching rows
											
			current_device = :DeviceNumber;  // Get the current site number
			current_site = :SiteNumber; 
											// Get the indices of rows that match the current SiteNumber
			matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
			start_row = Max( 1, Row() - 199 );
			end_row = Row();
			m = Subscript( Expr( testColumn ), matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
			// Calculate the quantiles and IQR
			q1 = Quantile( 0.25, m );
			q3 = Quantile( 0.75, m );
											
           // Calculate the upper limit using the sigma function
			q3 + (q3 - q1) * (slidervalue - 0.675) / 1.35;
		)
	)
);



end = Tick Seconds();

Show( end - start );

///UsingLoop
start2 = Tick Seconds();

llColX = dt << New Column( "LL_loop", Numeric );
ulColX = dt << New Column( "UL_loop", Numeric );



For Each Row(
	dt,
	If( Row() < 200,
		.,  
											// Skip the first 200 rows
		current_device = :DeviceNumber[Row()];  // Get the current site number
		current_site = :SiteNumber[Row()]; 
											// Get the indices of rows that match the current SiteNumber
		matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
           // Determine the start and end positions for the moving window
		start_row = Max( 1, Row() - 199 );
		end_row = Row();
           // Get the moving window data for matching rows
		m2 = Subscript( testColumn, matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
		// Calculate the quantiles and IQR
		q1 = Quantile( 0.25, m2 );
		q3 = Quantile( 0.75, m2 );
											
           // Calculate the lower limit using the sigma function
		llColX[Row()] = q1 - (q3 - q1) * (slidervalue - 0.675) / 1.35;
	);


	If( Row() < 200,
		.,  // Skip the first 200 rows
		
		// Get the moving window data for matching rows
											
		current_device = :DeviceNumber[Row()];  // Get the current site number
		current_site = :SiteNumber[Row()]; 
											// Get the indices of rows that match the current SiteNumber
		matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
		start_row = Max( 1, Row() - 199 );
		end_row = Row();
		m = Subscript( testColumn, matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
		// Calculate the quantiles and IQR
		q1 = Quantile( 0.25, m );
		q3 = Quantile( 0.75, m );
											
           // Calculate the upper limit using the sigma function
		ulColX[Row()] = q3 + (q3 - q1) * (slidervalue - 0.675) / 1.35;
	);
	
);
end2 = Tick Seconds();

Show( end2 - start2 );

Re: Need help with optimizing

jthi — Thu, 10 Oct 2024 18:00:09 GMT

First things to try are to utilize private/invisible table with << begin data update and << end data update. After that has been tried, then it might be a good idea to move into other methods

Re: Need help with optimizing

Mark_Bailey — Thu, 10 Oct 2024 18:10:46 GMT

You might want to use the Debugger and Profiler in JMP to determine where the script spends most of its time.

Re: Need help with optimizing

Jackie_ — Thu, 10 Oct 2024 18:32:20 GMT

Tried using begin/end data update and it made it worse
Total time : 780.73s

Names Default To Here( 1 );
dt = Current Data Table();

testColumn = Column( "Test_A" );

slidervalue = 6;

///UsingLoop
start2 = Tick Seconds();

llColX = dt << New Column( "LL_loop", Numeric );
ulColX = dt << New Column( "UL_loop", Numeric );


dt << show window( 0 );

dt << begin data update;
For Each Row(
	dt,
	If( Row() < 200,
		.,  
											// Skip the first 200 rows
		current_device = :DeviceNumber[Row()];  // Get the current site number
		current_site = :SiteNumber[Row()]; 
											// Get the indices of rows that match the current SiteNumber
		matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
           // Determine the start and end positions for the moving window
		start_row = Max( 1, Row() - 199 );
		end_row = Row();
           // Get the moving window data for matching rows
		m2 = Subscript( testColumn, matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
		// Calculate the quantiles and IQR
		q1 = Quantile( 0.25, m2 );
		q3 = Quantile( 0.75, m2 );
											
           // Calculate the lower limit using the sigma function
		llColX[Row()] = q1 - (q3 - q1) * (slidervalue - 0.675) / 1.35;
	);


	If( Row() < 200,
		.,  // Skip the first 200 rows
		
		// Get the moving window data for matching rows
											
		current_device = :DeviceNumber[Row()];  // Get the current site number
		current_site = :SiteNumber[Row()]; 
											// Get the indices of rows that match the current SiteNumber
		matching_rows = Where( :DeviceNumber == current_device | :SiteNumber == current_site );
		start_row = Max( 1, Row() - 199 );
		end_row = Row();
		m = Subscript( testColumn, matching_rows[Loc( matching_rows >= start_row & matching_rows <= end_row )] );
											//m = testColumn[Max( 1, Row() - 199 ) :: Row()];
		// Calculate the quantiles and IQR
		q1 = Quantile( 0.25, m );
		q3 = Quantile( 0.75, m );
											
           // Calculate the upper limit using the sigma function
		ulColX[Row()] = q3 + (q3 - q1) * (slidervalue - 0.675) / 1.35;
	);
	
);

dt << end data update;
end2 = Tick Seconds();

Show( end2 - start2 );

Re: Need help with optimizing

jthi — Thu, 10 Oct 2024 19:16:39 GMT

There are still things which could be improved but this took 10-15s on my PC

Names Default To Here(1);

dt = Open("$DOWNLOADS/sample_table.jmp");

slidervalue = 6;

start2 = Tick Seconds();

ds = dt[0, "DeviceNumber"];
ss = dt[0, "SiteNumber"];
m1 = J(1, N Rows(dt), .);
m2 = J(1, N Rows(dt), .);


For Each Row(dt,
	If(Row() < 200,
		.
	,
		d = :DeviceNumber[Row()];
		s = :SiteNumber[Row()];
		rs = (Row() - 199)::Row();
		
		d1 = ds[rs];
		s1 = ss[rs];
		
		r = where(s1 == s | d1 == d);
		
		vals = dt[rs[r], "Test_A"];
		
		q1 = Quantile(0.25, vals);
		q3 = Quantile(0.75, vals);
		
		m1[Row()] = q1 - (q3 - q1) * (slidervalue - 0.675) / 1.35;
		m2[Row()] = q3 + (q3 - q1) * (slidervalue - 0.675) / 1.35;
	);
);

llColX = dt << New Column("LL_loop1", Numeric, Continuous, Values(m1));
llColX = dt << New Column("UL_loop2", Numeric, Continuous, Values(m2));

end2 = Tick Seconds();
Show(end2 - start2);