我有一个相当大的 JMP 数据表（132k 行，2860 列），其中也有大量的公式列（200+）。我会定期将新数据导入此文件，因此我想保持所有公式列“实时”，以便在添加新数据时自动填充。我开始遇到公式重新计算速度的问题。
起初，这些公式大多是简单的算术（其他列值的加法/减法/乘法），速度不是问题。但是，我已经开始添加一些更复杂的公式，这大大增加了重新计算此表中所有公式所需的时间。
我使用 OnOpen 脚本自动抑制公式评估（因为首选项选项“在打开时抑制公式评估”不起作用），我使用数据导入脚本从 Excel 中引入新数据，评估公式，然后重新抑制公式评估。它已经到了需要 15 分钟才能完成导入脚本的程度，而 JMP 一直处于完全锁定状态并且没有响应。我可以查明需要花费 15 分钟大部分时间的几个特定公式列。
我想知道是否有人对如何优化计算速度有想法。例如：
- 也许我的 JSL 代码写的效率低下，可以改进吗？
- 这些公式只需要针对要添加的新行进行评估（通常一次不超过 50 行），但重新计算时间让我相信每次都会评估整个 132k 行。有没有办法对特定行进行部分评估？
这是一个较慢的公式之一的示例。这会根据表格每一行中的原始粘度值计算修正后的产品粘度。计算取决于产品等级，因此执行查找以获得特定于等级的信息。等级规格：查找是一个包含产品数据的关联数组，它在表的 OnOpen 脚本中定义。
Names Default To Here( 1 );
myGrade = :Name( "##run_grade_jmp" );
Try(
If( Namespace Exists( "GradeSpecs" ),
Visc_Property = GradeSpecs:find[myGrade]["Prod Visc"]["Property"];
If( Visc_Property == "xyzTestMethod",
xyz_Target = GradeSpecs:find[myGrade]["Prod Visc"]["Target"];
xyz_corrected_value = :Name( "xyz1_Value" ) * xyz_Target / :Name( "xyz2_Value" )) ^ 1.333;
,
xyz_corrected_value = Empty()
);
,
xyz_corrected_value = Empty()
)
,
xyz_corrected_value = Empty()
);
xyz_corrected_value;
这是另一个例子。函数 ThermoCH:RxEffDenZ 是在 OnOpen 脚本中定义的自定义函数。许多参数也在 ThermoCH 命名空间中定义为关联数组。
ThermoCH:RxEffDenZ(
:Pressure,
:Temp,
:z1conc,
(z2conc+z3conc),
(z4conc+z4conc),
0,
0,
0,
0
)
这是 RxEffDenZ 的定义：
ThermoCH:RxEffDenZ = Function(
//parameters
{dPressure_PSIG, dTemp_DEGF, dz4_wf, dz1_wf, dz2_wf, dz5_wf, dz6_wf, dz7_wf, dz3_wf},
//local variables
{, T1, T2, P3, TOTWF, iz4SF, z1SF, z2SF, z5SF, z7SF, z3SF, TOTMOL, Iz4, z1, z2, z5, z7, z3, Mw, ac_z1, ac_z2, ac_z4,
ac_z5, ac_z7, ac_z3, tr_z4, tr_z1, tr_z2, tr_z5, tr_z7, tr_z3, psat_z4, psat_z1, psat_z2, psat_z5, psat_z7, psat_z3, k_z4,
k_z1, k_z2, k_z5, k_z7, k_z3, alpha_z4, alpha_z1, alpha_z2, alpha_z5, alpha_z7, alpha_z3, a_z4, a_z1, a_z2, a_z5, a_z7, a_z3,
delta_1, delta_2, delta_3, delta_4, delta_5, delta_6, delta_7, delta_8, delta_9, delta_10, delta_11, delta_12, delta_13,
delta_14, delta_15, am, bm, pr_c1, pr_z1, pr_z2, q, r, D, radian, theta, v, i, S_1A, s_1, T_1A, T_1, den,
x1, x2, x3, S, T, Sprime, Tprime},
T1 = dTemp_DEGF + 459.67; //Convert F to R
T2 = T1 * (5 / 9); //Convert R to K
P3 = (dPressure_PSIG + 14.696) * 6894.757293178; //Convert psig to Pa
TOTWF = dz4_wf + dz1_wf + dz2_wf + dz5_wf + dz7_wf + dz3_wf;
iz4SF = dz4_wf / TOTWF;
z1SF = dz1_wf / TOTWF;
z2SF = dz2_wf / TOTWF;
z5SF = dz5_wf / TOTWF;
z7SF = dz7_wf / TOTWF;
z3SF = dz3_wf / TOTWF;
// Determine mole fraction of solvent components
TOTMOL = iz4SF / ThermoCH:MW["z4"] + z1SF / ThermoCH:MW["z1"] + z2SF / ThermoCH:MW["z2"] + z5SF / ThermoCH:MW["z5"] + z7SF
/ ThermoCH:MW["z7"] + z3SF / ThermoCH:MW["z3"];
Iz4 = iz4SF / ThermoCH:MW["z4"] / TOTMOL;
z1 = z1SF / ThermoCH:MW["z1"] / TOTMOL;
z2 = z2SF / ThermoCH:MW["z2"] / TOTMOL;
z5 = z5SF / ThermoCH:MW["z5"] / TOTMOL;
z7 = z7SF / ThermoCH:MW["z7"] / TOTMOL;
z3 = z3SF / ThermoCH:MW["z3"] / TOTMOL;
Mw = Iz4 * ThermoCH:MW["z4"] + z1 * ThermoCH:MW["z1"] + z2 * ThermoCH:MW["z2"] + z5 * ThermoCH:MW["z5"] + z7 * ThermoCH:MW[
"z7"] + z3 * ThermoCH:MW["z3"];
//a factors
ac_z4 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z4"] ^ 2 / (ThermoCH:Pc["z4"] * 10 ^ 5);
ac_z1 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z1"] ^ 2 / (ThermoCH:Pc["z1"] * 10 ^ 5);
ac_z2 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z2"] ^ 2 / (ThermoCH:Pc["z2"] * 10 ^ 5);
ac_z5 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z5"] ^ 2 / (ThermoCH:Pc["z5"] * 10 ^ 5);
ac_z7 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z7"] ^ 2 / (ThermoCH:Pc["z7"] * 10 ^ 5);
ac_z3 = 0.45724 * 8.3143 ^ 2 * ThermoCH:Tc["z3"] ^ 2 / (ThermoCH:Pc["z3"] * 10 ^ 5);
tr_z4 = T2 / ThermoCH:Tc["z4"];
tr_z1 = T2 / ThermoCH:Tc["z1"];
tr_z2 = T2 / ThermoCH:Tc["z2"];
tr_z5 = T2 / ThermoCH:Tc["z5"];
tr_z7 = T2 / ThermoCH:Tc["z7"];
tr_z3 = T2 / ThermoCH:Tc["z3"];
//psat in bar
psat_z4 = 10 ^ (6.9895 - 1216.92 / (227.451 + (dTemp_DEGF - 32) * (5 / 9))) * 0.019336721 * 0.068947573;
psat_z1 = 10 ^ (6.96636 - 649.806 / (262.73 + (dTemp_DEGF - 32) * (5 / 9))) * 0.019336721 * 0.068947573;
psat_z2 = 10 ^ (7.01612 - 860.992 / (255.895 + (dTemp_DEGF - 32) * (5 / 9))) * 0.019336721 * 0.068947573;
psat_z5 = 10 ^ (10.413 - 4466.47 / (447.523 + (dTemp_DEGF - 32) * (5 / 9))) * 0.019336721 * 0.068947573;
psat_z7 = 10 ^ (4.05752 - 1353.486 / (T2 + -60.386));
psat_z3 = 10 ^ (4.24696 - 1099.207 / (T2 + -8.256));
k_z4 = 0.37464 + 1.54226 * ThermoCH:w["z4"] - 0.26992 * ThermoCH:w["z4"] ^ 2;
k_z1 = 0.37464 + 1.54226 * ThermoCH:w["z1"] - 0.26992 * ThermoCH:w["z1"] ^ 2;
k_z2 = 0.37464 + 1.54226 * ThermoCH:w["z2"] - 0.26992 * ThermoCH:w["z2"] ^ 2;
k_z5 = 0.37464 + 1.54226 * ThermoCH:w["z5"] - 0.26992 * ThermoCH:w["z5"] ^ 2;
k_z7 = 0.37464 + 1.54226 * ThermoCH:w["z7"] - 0.26992 * ThermoCH:w["z7"] ^ 2;
k_z3 = 0.37464 + 1.54226 * ThermoCH:w["z3"] - 0.26992 * ThermoCH:w["z3"] ^ 2;
alpha_z4 = (1 + k_z4 * (1 - tr_z4 ^ (1 / 2))) ^ 2;
alpha_z1 = (1 + k_z1 * (1 - tr_z1 ^ (1 / 2))) ^ 2;
alpha_z2 = (1 + k_z2 * (1 - tr_z2 ^ (1 / 2))) ^ 2;
alpha_z5 = (1 + k_z5 * (1 - tr_z5 ^ (1 / 2))) ^ 2;
alpha_z7 = (1 + k_z7 * (1 - tr_z7 ^ (1 / 2))) ^ 2;
alpha_z3 = (1 + k_z3 * (1 - tr_z3 ^ (1 / 2))) ^ 2;
a_z4 = alpha_z4 * ac_z4;
a_z1 = alpha_z1 * ac_z1;
a_z2 = alpha_z2 * ac_z2;
a_z5 = alpha_z5 * ac_z5;
a_z7 = alpha_z7 * ac_z7;
a_z3 = alpha_z3 * ac_z3;
delta_1 = 0;
delta_2 = 0;
delta_3 = 0;
delta_4 = 0;
delta_5 = 0;
delta_6 = 0;
delta_7 = 0;
delta_8 = 0;
delta_9 = 0;
delta_10 = 0;
delta_11 = 0;
delta_12 = 0;
delta_13 = 0;
delta_14 = 0;
delta_15 = 0;
am = Iz4 ^ 2 * a_z4 + z1 ^ 2 * a_z1 + z2 ^ 2 * a_z2 + z5 ^ 2 * a_z5 + z7 ^ 2 * a_z7 + z3 ^ 2 * a_z3 + 2 * Iz4 * z1 * (1
-delta_1) * Sqrt( a_z4 * a_z1 ) + 2 * Iz4 * z2 * (1 - delta_2) * Sqrt( a_z4 * a_z2 ) + 2 * Iz4 * z5 * (1 - delta_3) *
Sqrt( a_z4 * a_z5 ) + 2 * Iz4 * z7 * (1 - delta_4) * Sqrt( a_z4 * a_z7 ) + 2 * Iz4 * z3 * (1 - delta_5) * Sqrt( a_z4 * a_z3 )
+ 2 * z1 * z2 * (1 - delta_6) * Sqrt( a_z1 * a_z2 ) + 2 * z1 * z5 * (1 - delta_7) * Sqrt( a_z1 * a_z5 ) + 2 * z1 * z7 *
(1 - delta_8) * Sqrt( a_z1 * a_z7 ) + 2 * z1 * z3 * (1 - delta_9) * Sqrt( a_z1 * a_z3 ) + 2 * z2 * z5 * (1 - delta_10) *
Sqrt( a_z2 * a_z5 ) + 2 * z2 * z7 * (1 - delta_11) * Sqrt( a_z2 * a_z7 ) + 2 * z2 * z3 * (1 - delta_12) *
Sqrt( a_z2 * a_z3 ) + 2 * z5 * z7 * (1 - delta_13) * Sqrt( a_z5 * a_z7 ) + 2 * z5 * z3 * (1 - delta_14) *
Sqrt( a_z5 * a_z3 ) + 2 * z7 * z3 * (1 - delta_15) * Sqrt( a_z7 * a_z3 );
bm = Iz4 * (0.0778 * 8.3143 * ThermoCH:Tc["z4"] / (ThermoCH:Pc["z4"] * 10 ^ 5)) + z1 * (0.0778 * 8.3143 * ThermoCH:Tc["z1"] / (
ThermoCH:Pc["z1"] * 10 ^ 5)) + z2 * (0.0778 * 8.3143 * ThermoCH:Tc["z2"] / (ThermoCH:Pc["z2"] * 10 ^ 5)) + z5 * (0.0778 *
8.3143 * ThermoCH:Tc["z5"] / (ThermoCH:Pc["z5"] * 10 ^ 5)) + z7 * (0.0778 * 8.3143 * ThermoCH:Tc["z7"] / (ThermoCH:Pc["z7"] * 10
^ 5)) + z3 * (0.0778 * 8.3143 * ThermoCH:Tc["z3"] / (ThermoCH:Pc["z3"] * 10 ^ 5));
pr_c1 = bm - 8.3143 * T2 / P3;
pr_z1 = am / P3 - 3 * bm ^ 2 - 2 * bm * 8.3143 * T2 / P3;
pr_z2 = bm ^ 3 + bm ^ 2 * 8.3143 * T2 / P3 - am * bm / P3;
q = -1 * (3 * pr_z1 - pr_c1 ^ 2) / 9;
r = -1 * (9 * pr_c1 * pr_z1 - 27 * pr_z2 - 2 * pr_c1 ^ 3) / 54;
D = r ^ 2 - q ^ 3;
//Discriminant: D < 0, all 3 roots are real and unequal; D = 0, all roots are real and at least two are equal
//D > 0, only one root is real and two are imaginary
// Calculate the first root
If( D < 0,
radian = ArcCosine( r / Sqrt( q ^ 3 ) );
x1 = -1 * (2 * Sqrt( q ) * Cos( (1 / 3) * radian )) - (1 / 3) * pr_c1;
x2 = -1 * (2 * Sqrt( q ) * Cos( (radian + 2 * Pi()) / 3 )) - (1 / 3) * pr_c1;
x3 = -1 * (2 * Sqrt( q ) * Cos( (radian - 2 * Pi()) / 3 )) - (1 / 3) * pr_c1;
v = x2;
,
Sprime = -1 * (r / Abs( r )) * (Abs( r ) + Sqrt( D )) ^ (1 / 3);
Tprime = q / Sprime;
x1 = Sprime + Tprime - (1 / 3) * pr_c1;
v = x1;
);
den = (1 / v) * Mw;
Return( den );
);过去，这些计算是由 Excel 使用 VBA UDF 完成的。 随着我对 JMP 的了解更多，我开始使用 JSL 代码重新实现它们，以减少对 Excel 的依赖。但是，我觉得我在这里开始达到 JMP 的能力极限，也许我无法在 JMP 中做这些事情。或者至少在使用数组查找和自定义函数时，我将被迫使用较小的数据集。
