This is an automated email from the ASF dual-hosted git repository.
caishunfeng pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new 7643d2840a [Improvement][api-sql] Optimize the sql for paging query
project (#13172)
7643d2840a is described below
commit 7643d2840af43b3e6c8934dfd9de0a647d36b850
Author: 陈家名 <[email protected]>
AuthorDate: Mon Dec 19 08:51:21 2022 +0800
[Improvement][api-sql] Optimize the sql for paging query project (#13172)
* Optimizing sql
* add version join condition
Co-authored-by: chenjiaming <[email protected]>
---
.../dolphinscheduler/dao/mapper/ProjectMapper.xml | 18 +++++++++++-------
1 file changed, 11 insertions(+), 7 deletions(-)
diff --git
a/dolphinscheduler-dao/src/main/resources/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml
b/dolphinscheduler-dao/src/main/resources/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml
index 32ef8e72a5..3495bab8a9 100644
---
a/dolphinscheduler-dao/src/main/resources/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml
+++
b/dolphinscheduler-dao/src/main/resources/org/apache/dolphinscheduler/dao/mapper/ProjectMapper.xml
@@ -84,15 +84,18 @@
select
<include refid="baseSqlV2">
<property name="alias" value="p"/>
- </include>
- ,
+ </include> ,
u.user_name as user_name,
- (SELECT COUNT(*) FROM t_ds_process_definition AS def WHERE
def.project_code = p.code) AS def_count,
- (SELECT COUNT(*) 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 AND def.project_code = p.code
- AND inst.state=1 ) as inst_running_count
+ count(distinct def.id) AS def_count,
+ count(distinct inst.id) 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
where 1=1
<if test="projectsIds != null and projectsIds.size() > 0">
and p.id in
@@ -105,7 +108,8 @@
OR p.description LIKE concat('%', #{searchName}, '%')
)
</if>
- order by p.create_time desc
+ group by p.id
+ order by p.id desc
</select>
<select id="queryAuthedProjectListByUserId"
resultType="org.apache.dolphinscheduler.dao.entity.Project">
select
