Skip to content

Commit ed036ef

Browse files
TommyLemonTommyLemon
committed
优化 Table[]:{ Table:{} } 这种单表数组的查询性能
1 parent 47961e3 commit ed036ef

3 files changed

Lines changed: 78 additions & 23 deletions

File tree

‎APIJSONORM/src/main/java/apijson/orm/AbstractObjectParser.java‎

Lines changed: 34 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ public AbstractObjectParser setParser(AbstractParser<?> parser) {
5858
protected boolean isSubquery;
5959

6060
protected final int type;
61+
protected final String arrayTable;
6162
protected final List<Join> joinList;
6263
protected final boolean isTable;
6364
protected final boolean isArrayMainTable;
@@ -86,6 +87,7 @@ public AbstractObjectParser(@NotNull JSONObject request, String parentPath, SQLC
8687
this.isSubquery = isSubquery;
8788

8889
this.type = arrayConfig == null ? 0 : arrayConfig.getType();
90+
this.arrayTable = arrayConfig == null ? null : arrayConfig.getTable();
8991
this.joinList = arrayConfig == null ? null : arrayConfig.getJoinList();
9092

9193
this.isTable = isTable; // apijson.JSONObject.isTableKey(table);
@@ -835,6 +837,7 @@ public Object onReferenceParse(@NotNull String path) {
835837
return parser.getValueByPath(path);
836838
}
837839

840+
@SuppressWarnings("unchecked")
838841
@Override
839842
public JSONObject onSQLExecute() throws Exception {
840843
int position = getPosition();
@@ -846,30 +849,47 @@ public JSONObject onSQLExecute() throws Exception {
846849
else {
847850
result = parser.executeSQL(sqlConfig, isSubquery);
848851

849-
if (isArrayMainTable && position == 0 && result != null) { // 提取并缓存数组主表的列表数据
850-
@SuppressWarnings("unchecked")
851-
List<JSONObject> list = (List<JSONObject>) result.remove(SQLExecutor.KEY_RAW_LIST);
852-
if (list != null) {
852+
boolean isSimpleArray = false;
853+
List<JSONObject> rawList = null;
854+
855+
if (isArrayMainTable && position == 0 && result != null) {
856+
857+
isSimpleArray = (functionMap == null || functionMap.isEmpty())
858+
&& (customMap == null || customMap.isEmpty())
859+
&& (table.equals(arrayTable));
860+
861+
// 提取并缓存数组主表的列表数据
862+
rawList = (List<JSONObject>) result.remove(SQLExecutor.KEY_RAW_LIST);
863+
if (rawList != null) {
853864
String arrayPath = parentPath.substring(0, parentPath.lastIndexOf("[]") + 2);
854865

855-
long startTime = System.currentTimeMillis();
856-
for (int i = 1; i < list.size(); i++) { // 从 1 开始，0 已经处理过
857-
JSONObject obj = list.get(i);
858866

859-
if (obj != null) {
860-
parser.putQueryResult(arrayPath + "/" + i + "/" + name, obj); //解决获取关联数据时requestObject里不存在需要的关联数据
867+
if (isSimpleArray == false) {
868+
long startTime = System.currentTimeMillis();
869+
870+
for (int i = 1; i < rawList.size(); i++) { // 从 1 开始，0 已经处理过
871+
JSONObject obj = rawList.get(i);
872+
873+
if (obj != null) {
874+
parser.putQueryResult(arrayPath + "/" + i + "/" + name, obj); // 解决获取关联数据时requestObject里不存在需要的关联数据
875+
}
861876
}
877+
878+
long endTime = System.currentTimeMillis(); // 3ms - 8ms
879+
Log.e(TAG, "\n onSQLExecute <<<<<<<<<<<<<<<<<<<<<<<<<<<<\n for (int i = 1; i < list.size(); i++) startTime = " + startTime
880+
+ "; endTime = " + endTime + "; duration = " + (endTime - startTime) + "\n >>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n ");
862881
}
863-
864-
long endTime = System.currentTimeMillis();
865-
Log.e(TAG, "onSQLExecute for (int i = 1; i < list.size(); i++) startTime = " + startTime + "; endTime = " + endTime + "; duration = " + (endTime - startTime));
866882

867-
parser.putArrayMainCache(arrayPath, list);
883+
parser.putArrayMainCache(arrayPath, rawList);
868884
}
869885
}
870886

871887
if (isSubquery == false && result != null) {
872-
parser.putQueryResult(path, result);//解决获取关联数据时requestObject里不存在需要的关联数据
888+
parser.putQueryResult(path, result); // 解决获取关联数据时requestObject里不存在需要的关联数据
889+
890+
if (isSimpleArray && rawList != null) {
891+
result.put(SQLExecutor.KEY_RAW_LIST, rawList);
892+
}
873893
}
874894
}
875895

‎APIJSONORM/src/main/java/apijson/orm/AbstractParser.java‎

Lines changed: 41 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1031,10 +1031,14 @@ public JSONArray onArrayParse(JSONObject request, String parentPath, String name
10311031
int index = isSubquery || name == null ? -1 : name.lastIndexOf("[]");
10321032
String childPath = index <= 0 ? null : Pair.parseEntry(name.substring(0, index), true).getKey(); // Table-key1-key2...
10331033

1034+
String arrTableKey = null;
10341035
//判断第一个key，即Table是否存在，如果存在就提取
10351036
String[] childKeys = StringUtil.split(childPath, "-", false);
10361037
if (childKeys == null || childKeys.length <= 0 || request.containsKey(childKeys[0]) == false) {
10371038
childKeys = null;
1039+
}
1040+
else if (childKeys.length == 1 && JSONRequest.isTableKey(childKeys[0])) { // 可能无需提取，直接返回 rawList 即可
1041+
arrTableKey = childKeys[0];
10381042
}
10391043

10401044

@@ -1045,25 +1049,46 @@ public JSONArray onArrayParse(JSONObject request, String parentPath, String name
10451049
.setCount(size)
10461050
.setPage(page)
10471051
.setQuery(query2)
1052+
.setTable(arrTableKey)
10481053
.setJoinList(onJoinParse(join, request));
10491054

10501055
JSONObject parent;
10511056

1052-
long startTime = System.currentTimeMillis();
1057+
boolean isExtract = true;
1058+
10531059
//生成size个
10541060
for (int i = 0; i < (isSubquery ? 1 : size); i++) {
10551061
parent = onObjectParse(request, isSubquery ? parentPath : path, isSubquery ? name : "" + i, config.setType(SQLConfig.TYPE_ITEM).setPosition(i), isSubquery);
10561062
if (parent == null || parent.isEmpty()) {
10571063
break;
10581064
}
10591065

1066+
long startTime = System.currentTimeMillis();
1067+
1068+
/* 这里优化了 Table[]: { Table:{} } 这种情况下的性能
1069+
* 如果把 List<JSONObject> 改成 JSONArray 来减少以下 addAll 一次复制，则会导致 AbstractSQLExecutor 等其它很多地方 get 要改为 getJSONObject，
1070+
* 修改类型会导致不兼容旧版依赖 ORM 的项目，而且整体上性能只有特殊情况下性能提升，其它非特殊情况下因为多出很多 instanceof JSONObject 的判断而降低了性能。
1071+
*/
1072+
JSONObject fo = i != 0 || arrTableKey == null ? null : parent.getJSONObject(arrTableKey);
1073+
@SuppressWarnings("unchecked")
1074+
List<JSONObject> list = fo == null ? null : (List<JSONObject>) fo.remove(SQLExecutor.KEY_RAW_LIST);
1075+
1076+
if (list != null && list.isEmpty() == false) {
1077+
isExtract = false;
1078+
1079+
list.set(0, fo); // 不知道为啥第 0 项也加了 @RAW@LIST
1080+
response.addAll(list); // List<JSONObject> cannot match List<Object> response = new JSONArray(list);
1081+
1082+
long endTime = System.currentTimeMillis(); // 0ms
1083+
Log.d(TAG, "\n onArrayParse <<<<<<<<<<<<<<<<<<<<<<<<<<<<\n for (int i = 0; i < (isSubquery ? 1 : size); i++) "
1084+
+ " startTime = " + startTime + "; endTime = " + endTime + "; duration = " + (endTime - startTime) + "\n >>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");
1085+
break;
1086+
}
1087+
10601088
//key[]:{Table:{}}中key equals Table时 提取Table
10611089
response.add(getValue(parent, childKeys)); //null有意义
10621090
}
10631091

1064-
long endTime = System.currentTimeMillis();
1065-
Log.e(TAG, "onArrayParse for for (int i = 0; i < (isSubquery ? 1 : size); i++) startTime = " + startTime + "; endTime = " + endTime + "; duration = " + (endTime - startTime));
1066-
10671092
//Table>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
10681093

10691094

@@ -1082,12 +1107,20 @@ public JSONArray onArrayParse(JSONObject request, String parentPath, String name
10821107
}
10831108
}
10841109
*/
1085-
Object fo = childKeys == null || response.isEmpty() ? null : response.get(0);
1086-
if (fo instanceof Boolean || fo instanceof Number || fo instanceof String) { //[{}] 和 [[]] 都没意义
1087-
putQueryResult(path, response);
1110+
if (isExtract) {
1111+
long startTime = System.currentTimeMillis();
1112+
1113+
Object fo = childKeys == null || response.isEmpty() ? null : response.get(0);
1114+
if (fo instanceof Boolean || fo instanceof Number || fo instanceof String) { //[{}] 和 [[]] 都没意义
1115+
putQueryResult(path, response);
1116+
}
1117+
1118+
long endTime = System.currentTimeMillis();
1119+
Log.d(TAG, "\n onArrayParse <<<<<<<<<<<<<<<<<<<<<<<<<<<<\n isExtract >> putQueryResult "
1120+
+ " startTime = " + startTime + "; endTime = " + endTime + "; duration = " + (endTime - startTime) + "\n >>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");
10881121
}
1089-
} finally {
10901122

1123+
} finally {
10911124
//后面还可能用到，要还原
10921125
request.put(JSONRequest.KEY_QUERY, query);
10931126
request.put(JSONRequest.KEY_COUNT, count);

‎APIJSONORM/src/main/java/apijson/orm/SQLConfig.java‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -134,6 +134,9 @@ public interface SQLConfig {
134134
* @see {@link #getSQLTable()}
135135
*/
136136
String getTable();
137+
138+
SQLConfig setTable(String table);
139+
137140
/**数据库里的真实Table名
138141
* 通过 {@link #TABLE_KEY_MAP} 映射
139142
* @return
@@ -145,7 +148,6 @@ public interface SQLConfig {
145148
List<String> getRaw();
146149
SQLConfig setRaw(List<String> raw);
147150

148-
SQLConfig setTable(String table);
149151

150152
String getGroup();
151153
SQLConfig setGroup(String group);

0 commit comments

Comments
 (0)