extjs 动态加载列表，优化思路
2024-09-01 23:46:25
功能截图
之前做法，先查询每一行的前4个字段，然后动态拼接出其他的字段，效率极低，以下是优化后的代码，供参考，只提供一个优化思路，授人以鱼不如授人以渔
后台Sql语句优化(语法仅支持Oracle)
SELECT PROJ_NO,
ALL_SECTION,
GRAND_SEC,
SPCID,
FIR_SCH_ID_1166,
BEGIN_DATE_1166,
END_DATE_1166,
FLOAT_CYC_1166,
FIR_SCH_ID_1167,
BEGIN_DATE_1167,
END_DATE_1167,
FLOAT_CYC_1167,
FIR_SCH_ID_1168,
BEGIN_DATE_1168,
END_DATE_1168,
FLOAT_CYC_1168,
FIR_SCH_ID_1169,
BEGIN_DATE_1169,
END_DATE_1169,
FLOAT_CYC_1169,
FIR_SCH_ID_1170,
BEGIN_DATE_1170,
END_DATE_1170,
FLOAT_CYC_1170,
FIR_SCH_ID_1171,
BEGIN_DATE_1171,
END_DATE_1171,
FLOAT_CYC_1171,
FIR_SCH_ID_1172,
BEGIN_DATE_1172,
END_DATE_1172,
FLOAT_CYC_1172,
FIR_SCH_ID_1173,
BEGIN_DATE_1173,
END_DATE_1173,
FLOAT_CYC_1173,
FIR_SCH_ID_1174,
BEGIN_DATE_1174,
END_DATE_1174,
FLOAT_CYC_1174,
FIR_SCH_ID_1175,
BEGIN_DATE_1175,
END_DATE_1175,
FLOAT_CYC_1175,
FIR_SCH_ID_1176,
BEGIN_DATE_1176,
END_DATE_1176,
FLOAT_CYC_1176,
FIR_SCH_ID_1177,
BEGIN_DATE_1177,
END_DATE_1177,
FLOAT_CYC_1177,
FIR_SCH_ID_1178,
BEGIN_DATE_1178,
END_DATE_1178,
FLOAT_CYC_1178,
FIR_SCH_ID_1179,
BEGIN_DATE_1179,
END_DATE_1179,
FLOAT_CYC_1179,
ALL_SECTION_BEGIN_DATE,
ALL_SECTION_BEGIN_DATE2
FROM (SELECT Y.PROJ_NO,
Y.SPCID,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1166,
, Y.BEGIN_DATE)) BEGIN_DATE_1166,
, Y.END_DATE)) END_DATE_1166,
, Y.FLOAT_CYC)) FLOAT_CYC_1166,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1167,
, Y.BEGIN_DATE)) BEGIN_DATE_1167,
, Y.END_DATE)) END_DATE_1167,
, Y.FLOAT_CYC)) FLOAT_CYC_1167,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1168,
, Y.BEGIN_DATE)) BEGIN_DATE_1168,
, Y.END_DATE)) END_DATE_1168,
, Y.FLOAT_CYC)) FLOAT_CYC_1168,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1169,
, Y.BEGIN_DATE)) BEGIN_DATE_1169,
, Y.END_DATE)) END_DATE_1169,
, Y.FLOAT_CYC)) FLOAT_CYC_1169,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1170,
, Y.BEGIN_DATE)) BEGIN_DATE_1170,
, Y.END_DATE)) END_DATE_1170,
, Y.FLOAT_CYC)) FLOAT_CYC_1170,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1171,
, Y.BEGIN_DATE)) BEGIN_DATE_1171,
, Y.END_DATE)) END_DATE_1171,
, Y.FLOAT_CYC)) FLOAT_CYC_1171,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1172,
, Y.BEGIN_DATE)) BEGIN_DATE_1172,
, Y.END_DATE)) END_DATE_1172,
, Y.FLOAT_CYC)) FLOAT_CYC_1172,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1173,
, Y.BEGIN_DATE)) BEGIN_DATE_1173,
, Y.END_DATE)) END_DATE_1173,
, Y.FLOAT_CYC)) FLOAT_CYC_1173,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1174,
, Y.BEGIN_DATE)) BEGIN_DATE_1174,
, Y.END_DATE)) END_DATE_1174,
, Y.FLOAT_CYC)) FLOAT_CYC_1174,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1175,
, Y.BEGIN_DATE)) BEGIN_DATE_1175,
, Y.END_DATE)) END_DATE_1175,
, Y.FLOAT_CYC)) FLOAT_CYC_1175,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1176,
, Y.BEGIN_DATE)) BEGIN_DATE_1176,
, Y.END_DATE)) END_DATE_1176,
, Y.FLOAT_CYC)) FLOAT_CYC_1176,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1177,
, Y.BEGIN_DATE)) BEGIN_DATE_1177,
, Y.END_DATE)) END_DATE_1177,
, Y.FLOAT_CYC)) FLOAT_CYC_1177,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1178,
, Y.BEGIN_DATE)) BEGIN_DATE_1178,
, Y.END_DATE)) END_DATE_1178,
, Y.FLOAT_CYC)) FLOAT_CYC_1178,
, Y.FIR_SCH_ID)) FIR_SCH_ID_1179,
, Y.BEGIN_DATE)) BEGIN_DATE_1179,
, Y.END_DATE)) END_DATE_1179,
, Y.FLOAT_CYC)) FLOAT_CYC_1179,
Y.ALL_SECTION,
Y.ALL_SECTION_BEGIN_DATE,
Y.ALL_SECTION_BEGIN_DATE2,
Y.GRAND_SEC
FROM (SELECT FSI.PROJ_NO,
FSI.SPCID,
FSI.STANDA_SCHEDULE_NODE_ID,
SSN.NODE_NAME,
SSN.NODE_SEQ,
FSI.BEGIN_DATE,
FSI.END_DATE,
FSI.FLOAT_CYC,
FSI.ALL_SECTION,
FSI.ALL_SECTION_BEGIN_DATE,
FSI.ALL_SECTION_BEGIN_DATE2,
FSI.FIR_SCH_ID,
FSI.GRAND_SEC
FROM FIRST_SCHEDULE_INFO_VIEW FSI
LEFT JOIN STANDA_SCHEDULE_NODE SSN
ON SSN.STANDA_SCHEDULE_NODE_ID =
FSI.STANDA_SCHEDULE_NODE_ID
) Y
GROUP BY Y.PROJ_NO,
Y.SPCID,
Y.ALL_SECTION,
Y.ALL_SECTION_BEGIN_DATE,
Y.GRAND_SEC,
Y.ALL_SECTION_BEGIN_DATE2)
后台逻辑，拼接Sql语句
string ssnSql = string.Format("SELECT T.* FROM STANDA_SCHEDULE_NODE T WHERE T.STANDA_SCHEDULE_ID = {1} AND T.IS_DEL = '8' {0} ORDER BY T.NODE_SEQ", StringUtil.isNotNullOrBlank(ssnIds) ? "AND T.STANDA_SCHEDULE_NODE_ID IN (" + ssnIds.Replace(DbUtil.colSplit, ',') + ")" : "", PlanUtil.FirstScheduleS);
DataTable ssndt = DbUtil.QueryDT(ssnSql);
StringBuilder sb = new StringBuilder();
)
{
List<Standa_Schedule_Node> ssnList = BeanUtil.ToEntity<Standa_Schedule_Node>(ssndt);
foreach (Standa_Schedule_Node ssn in ssnList)
{
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.FIR_SCH_ID)) FIR_SCH_ID_{0},", ssn.Standa_Schedule_Node_Id));
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.BEGIN_DATE)) BEGIN_DATE_{0},", ssn.Standa_Schedule_Node_Id));
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.END_DATE)) END_DATE_{0},", ssn.Standa_Schedule_Node_Id));
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.FLOAT_CYC)) FLOAT_CYC_{0},", ssn.Standa_Schedule_Node_Id));
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.E_BEGIN_DATE)) E_BEGIN_DATE_{0},", ssn.Standa_Schedule_Node_Id));
sb.Append(string.Format("MAX(DECODE(Y.STANDA_SCHEDULE_NODE_ID, {0}, Y.E_END_DATE)) E_END_DATE_{0},", ssn.Standa_Schedule_Node_Id));
}
}
string conditionSql = "";
conditionSql = sb.ToString();
string querySql = @"SELECT {2}
FROM (SELECT Y.PROJ_NO,
Y.SPCID,
{0} Y.ALL_SECTION,
Y.ALL_SECTION_BEGIN_DATE,
Y.ALL_SECTION_BEGIN_DATE2,
Y.GRAND_SEC
FROM (SELECT FSI.PROJ_NO,
FSI.SPCID,
FSI.STANDA_SCHEDULE_NODE_ID,
SSN.NODE_NAME,
SSN.NODE_SEQ,
FSI.BEGIN_DATE,
FSI.END_DATE,
FSI.E_BEGIN_DATE,
FSI.E_END_DATE,
FSI.FLOAT_CYC,
FSI.ALL_SECTION,
FSI.ALL_SECTION_BEGIN_DATE,
FSI.ALL_SECTION_BEGIN_DATE2,
FSI.FIR_SCH_ID,
FSI.GRAND_SEC
FROM FIRST_SCHEDULE_INFO_VIEW FSI
LEFT JOIN STANDA_SCHEDULE_NODE SSN
ON SSN.STANDA_SCHEDULE_NODE_ID =
FSI.STANDA_SCHEDULE_NODE_ID {1}) Y
GROUP BY Y.PROJ_NO,
Y.SPCID,
Y.ALL_SECTION,
Y.ALL_SECTION_BEGIN_DATE,
Y.GRAND_SEC,
Y.ALL_SECTION_BEGIN_DATE2)";
if (StringUtil.isNotNullOrBlank(v.Page) && v.Page != "-1")
{
;
v.Dt = DbUtil.Query(String.Format(querySql, conditionSql, DbUtil.WHEREOTO + v.getCondition(), StringUtil.nullOrBlankTOStr(v.DFields, "*")), v.Start, v.Limit, ref total);
v.Total = total;
}
else
{
v.Dt = DbUtil.QueryDT(string.Format(querySql, conditionSql, DbUtil.WHEREOTO + v.getCondition(), StringUtil.nullOrBlankTOStr(v.DFields, "*")));
}
return v.Dt;
Oracle Decode函数用法，可参考：https://www.cnblogs.com/Lightning-Kid/p/3954202.html
最新文章
- ARM-ContexM3/4组优先级和子优先级抢占规则
- 使用SQL Server Audit记录数据库变更
- markdown使用总结
- NEC学习 ---- 模块 -文本圆角背景导航
- Access数据库中Sum函数返回空值（Null）时如何设置为0
- charles使用教程指南（抓包工具）
- map reduce filter
- Octopus系列之HttpCustom2.0模板引擎的处理，一个bug的分析
- Qt 4.6: A Quick Start to Qt Designer
- Go如何发送广播包
- 扫码JSP
- 查看和删除chrome浏览器缓存内容
- JDK内置工具之一——JMap(java memory map)
- sprigboot recontroller 是responsebody与controller结合 这样 就使每个方法默认返回json
- 博客地址更改为csdn博客：https://blog.csdn.net/zysps1
- Billiard CFR484 div2 （数论）
- 062 SparkStream内部原理
- 实现两个矩阵相乘的C语言程序
- JavaScript Promise迷你书（中文版）
- React——JSX