804877_fr-FR

liliconcom

Besoin d'aide pour optimiser

Bonjour,

Je souhaite optimiser le calcul. J'ai essayé à la fois Col Formula et Loop, mais aucun des deux ne semble être plus rapide pour les lignes > 10 000.
J'ai comparé la vitesse de l'exe et les deux prennent beaucoup de temps

Formule Col = 500,46 s ;
Boucle = 491,70 s ;

Existe-t-il un moyen d'optimiser à l'aide de Matrix ou de 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 );

Automatisation et scriptsRe: Besoin d'aide pour optimiser

La première chose à essayer est d'utiliser une table privée/invisible avec << début de la mise à jour des données et << fin de la mise à jour des données. Après avoir essayé cela, il peut être judicieux de passer à d'autres méthodes

Re: Besoin d'aide pour optimiser

Vous souhaiterez peut-être utiliser le débogueur et le profileur dans JMP pour déterminer où le script passe la majeure partie de son temps.

Re: Besoin d'aide pour optimiser

J'ai essayé d'utiliser la mise à jour des données de début/fin et cela a empiré la situation.
Durée totale : 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: Besoin d'aide pour optimiser

Il y a encore des choses qui pourraient être améliorées, mais cela a pris 10 à 15 secondes sur mon 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);