You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by "chen877511127 (via GitHub)" <gi...@apache.org> on 2024/01/10 02:26:16 UTC
[PR] Project list query is slow [dolphinscheduler]
chen877511127 opened a new pull request, #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452
Project list query is slow When t_ds_process_instance table data reaches 300000, Project management list query takes more than 6 seconds
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "SbloodyS (via GitHub)" <gi...@apache.org>.
SbloodyS closed pull request #15452: [improvement][API]Project list query is slow
URL: https://github.com/apache/dolphinscheduler/pull/15452
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "fuchanghai (via GitHub)" <gi...@apache.org>.
fuchanghai commented on PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#issuecomment-1884237360
BTW ， your current aggregate statistics still check all projects. I suggest that the statistics code is best written separately. For example, the following SQL code only checks the aggregate statistics of the project on the current page.
```sql
<select id="queryRunningProcessInstanceNumber" resultType="org.apache.dolphinscheduler.dao.model.ProjectInstanceRunningDTO">
SELECT COUNT(*) as inst_running_count,def.project_code FROM t_ds_process_definition_log def, t_ds_process_instance inst WHERE def.code =
inst.process_definition_code and def.version = inst.process_definition_version
<if test="projectsCodes != null and projectsCodes.size() > 0">
and def.project_code in
<foreach item="code" index="index" collection="projectsCodes" open="(" separator="," close=")">
#{code}
</foreach>
</if>
AND inst.state=1 group by def.project_code
</select>
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "SbloodyS (via GitHub)" <gi...@apache.org>.
SbloodyS commented on PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#issuecomment-1886166028
Agreed with @fuchanghai .
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "fuchanghai (via GitHub)" <gi...@apache.org>.
fuchanghai commented on PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#issuecomment-1884568010
#15425
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "ruanwenjun (via GitHub)" <gi...@apache.org>.
ruanwenjun commented on code in PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#discussion_r1455660073
##########
dolphinscheduler-dao/src/main/resources/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml:
##########
@@ -87,15 +87,20 @@
</include> ,
u.user_name as user_name,
count(distinct def.id) AS def_count,
- count(distinct inst.id) as inst_running_count
+ t.inst_running_count as inst_running_count
from t_ds_project p
left join t_ds_user u on u.id=p.user_id
left join t_ds_process_definition def
on def.project_code = p.code
- left join t_ds_process_instance inst
- on inst.process_definition_code = def.code
- and inst.process_definition_version = def.version
- and inst.state = 1
+ left join (select
+ COUNT(distinct inst.id) as inst_running_count, def.project_code code
+ from
+ t_ds_process_definition_log def
+ left join t_ds_process_instance inst on def.code = inst.process_definition_code
+ and def.version = inst.process_definition_version
+ where
+ inst.state = 1
+ group by def.project_code) t on t.code = p.code
Review Comment:
Can we remove the join sql? It's difficult to maintain.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "fuchanghai (via GitHub)" <gi...@apache.org>.
fuchanghai commented on code in PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#discussion_r1457028767
##########
dolphinscheduler-api/src/main/java/org/apache/dolphinscheduler/api/service/impl/ProjectServiceImpl.java:
##########
@@ -855,4 +862,11 @@ public List<Long> getAuthorizedProjectCodes(User loginUser) {
.map(Project::getCode)
.collect(Collectors.toList());
}
+
+ private void setRunningProcessInstanceNumber(List<Project> projectList) {
+ List<Long> projectsCodes = projectList.stream().map(Project::getCode).collect(Collectors.toList());
+ List<ProjectInstanceRunningDTO> projectInstanceRunningDTOList = projectMapper.queryRunningProcessInstanceNumber(projectsCodes);
Review Comment:
hi @chen877511127 i suggest that call method of queryRunningProcessInstanceNumber when ```projectsCodes.size()>0```,
Otherwise, the SQL statement of the queryRunningProcessInstanceNumber method will not trigger the ```<if test="projectsCodes != null and projectsCodes.size() > 0"> ```, and all project information will be queried.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "SbloodyS (via GitHub)" <gi...@apache.org>.
SbloodyS commented on PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#issuecomment-2247001936
Fixed in #16341
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "chen877511127 (via GitHub)" <gi...@apache.org>.
chen877511127 commented on PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#issuecomment-1888422153
1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [improvement][API]Project list query is slow [dolphinscheduler]
Posted by "chen877511127 (via GitHub)" <gi...@apache.org>.
chen877511127 commented on code in PR #15452:
URL: https://github.com/apache/dolphinscheduler/pull/15452#discussion_r1469013453
##########
dolphinscheduler-api/src/main/java/org/apache/dolphinscheduler/api/service/impl/ProjectServiceImpl.java:
##########
@@ -855,4 +862,11 @@ public List<Long> getAuthorizedProjectCodes(User loginUser) {
.map(Project::getCode)
.collect(Collectors.toList());
}
+
+ private void setRunningProcessInstanceNumber(List<Project> projectList) {
+ List<Long> projectsCodes = projectList.stream().map(Project::getCode).collect(Collectors.toList());
+ List<ProjectInstanceRunningDTO> projectInstanceRunningDTOList = projectMapper.queryRunningProcessInstanceNumber(projectsCodes);
Review Comment:
I don't know what else I need to do? When can my submission be merged?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org