我有一個相當大的 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 中做這些事情。或者至少在使用數組查找和自訂函數時我將被迫使用較小的資料集。
