804877_es-ES

liliconcom

¿Necesitas ayuda para optimizar?

Hola,

Quiero optimizar el cálculo. Probé Col Formula y Loop, pero ninguno de ellos parece ser más rápido para filas > 10k.
Comparé la velocidad del exe y ambos tardan mucho tiempo.

Fórmula Col = 500,46s;
Bucle = 491,70 s;

¿Hay alguna forma de optimizar usando Matrix o 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 );

Automatización y scriptingRe: Necesito ayuda con la optimización

Lo primero que se debe intentar es utilizar una tabla privada/invisible con << inicio de actualización de datos y << fin de actualización de datos. Una vez que se haya probado esto, puede ser una buena idea pasar a otros métodos.

Re: Necesito ayuda con la optimización

Es posible que desees utilizar el depurador y el generador de perfiles en JMP para determinar dónde el script pasa la mayor parte de su tiempo.

Re: Necesito ayuda con la optimización

Intenté usar la actualización de datos de inicio/fin y empeoró la situación.
Tiempo total: 780,73 s

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: Necesito ayuda con la optimización

Todavía hay cosas que podrían mejorarse, pero esto tardó entre 10 y 15 segundos en mi 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);