我的位置：首页 › 一站式数据分析平台-亿信ABI › 亿信ABI-问题求助 › 添加topN=10，查询速度慢

[ABI 5.1] 添加topN=10，查询速度慢

复制链接 | 收藏本帖

11827

: 亿信小白初学数据Lv2

发表于2020-12-4 11:47

悬赏1

已解决

楼主

添加topN=10，查询速度一下变成二十多秒，不添加topN条件，查询速度很快，为什么只一个条件，查询速度变这么慢？怎么解决？1. 未添加topN条件

执行的sql语句
================GRID3================
2020-12-04 11:40:15 在并行计算线程池中，开始计算表格GRID3的第1个分析区（0.0.5.2）！
2020-12-04 11:40:15 正在生成SQL...
2020-12-04 11:40:15 计算表格GRID3
2020-12-04 11:40:15 生成SQL耗时37毫秒
2020-12-04 11:40:15 当前连接池为【ABISYS】，执行SQL:
select * from ( select a.C2 as C2,b.D2 as D2,b.E2 as E2,a.B2 as B2
from (
select MAX(a.QYMC) as C2,a.YHBH as B2
from F_YCSO375 a
where (((a.QYMC is not null and a.QYMC<>'')) AND ((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) a
inner join (
select MAX(a.QX) as D2,sum(a.YDL) as E2,a.YHBH as B2
from F_YCSO375 a
where (((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) b
on (a.B2=b.B2)
order by b.E2 desc ) as temp_ limit 1000
2020-12-04 11:40:15 当前连接池为【ABISYS】，执行SQL:
select count(*) as cn_
from (
select a.C2 as C2
from (
select MAX(a.QYMC) as C2,a.YHBH as B2
from F_YCSO375 a
where (((a.QYMC is not null and a.QYMC<>'')) AND ((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) a
inner join (
select MAX(a.QX) as D2,sum(a.YDL) as E2,a.YHBH as B2
from F_YCSO375 a
where (((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) b
on (a.B2=b.B2)) a
2020-12-04 11:40:16 SQL执行完毕，查询出2,239条数据，耗时：164毫秒（开始时间：2020-12-04 11:40:15.856；结束时间：2020-12-04 11:40:16.020）；其中生成SQL耗时37毫秒；初始获取连接耗时：0毫秒；查询准备总耗时：2毫秒；执行查询sql耗时：73毫秒；执行计数sql耗时：37毫秒；
2020-12-04 11:40:16 在并行计算线程池中，完成计算表格GRID3第1个分析区（0.0.5.2）！
================GRID3 END================

2. topN=10时，查询速度到二十多秒

执行的sql语句：
================GRID3================
2020-12-04 11:38:55 在并行计算线程池中，开始计算表格GRID3的第1个分析区（0.0.5.2）！
2020-12-04 11:38:55 正在生成SQL...
2020-12-04 11:38:55 计算表格GRID3
2020-12-04 11:38:55 生成SQL耗时24毫秒
2020-12-04 11:38:55 当前连接池为【ABISYS】，执行SQL:
select * from ( select a.C2 as C2,a.D2 as D2,a.E2 as E2,a.B2 as B2,count(b.E2) as E2_sortn
from (
select a.C2 as C2,b.D2 as D2,b.E2 as E2,a.B2 as B2
from (
select MAX(a.QYMC) as C2,a.YHBH as B2
from F_YCSO375 a
where (((a.QYMC is not null and a.QYMC<>'')) AND ((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) a
inner join (
select MAX(a.QX) as D2,sum(a.YDL) as E2,a.YHBH as B2
from F_YCSO375 a
where (((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) b
on (a.B2=b.B2)) a
,(
select a.C2 as C2,b.D2 as D2,b.E2 as E2,a.B2 as B2
from (
select MAX(a.QYMC) as C2,a.YHBH as B2
from F_YCSO375 a
where (((a.QYMC is not null and a.QYMC<>'')) AND ((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) a
inner join (
select MAX(a.QX) as D2,sum(a.YDL) as E2,a.YHBH as B2
from F_YCSO375 a
where (((a.TJZQ>=str_to_date('20201101','%Y%m%d') and a.TJZQ<str_to_date('20201102','%Y%m%d'))) AND (a.DLTJZQ = '2020年10月'))
group by a.YHBH) b
on (a.B2=b.B2)) b
where (a.E2<=b.E2)
group by a.B2,a.E2,a.D2,a.C2
having (count(b.E2)<=10)
order by E2_sortn ) as temp_ limit 1000
2020-12-04 11:39:19 SQL执行完毕，查询出10条数据，耗时：23秒680毫秒（开始时间：2020-12-04 11:38:55.958；结束时间：2020-12-04 11:39:19.638）；其中生成SQL耗时24毫秒；初始获取连接耗时：1毫秒；查询准备总耗时：2毫秒；执行查询sql耗时：23秒654毫秒；
2020-12-04 11:39:19 在并行计算线程池中，完成计算表格GRID3第1个分析区（0.0.5.2）！
================GRID3 END================