当前位置:网站首页>Gbase 8A slow SQL optimization case

Gbase 8A slow SQL optimization case

2022-01-15 02:18:21 CSDN Q & A

insert select Execution time exceeded 2 Hours
insert into tableABCD

select distinct cast(ct.USER_ID as char) as USER_ID
from datumdb.dwa_grp_mem_info_d_20211031 dgmid
join datumdb.dwa_grp_mem_use_info_m_202110 ct
on cast(ct.GROUP_ID as char) = cast(dgmid.GROUP_ID as char);

Execution time exceeded 2 Hours
There is no optimization idea
Optimize execution performance



Refer to the answer 1:

At first, I was suspicious because the associated field on the left ct.GROUP_ID Added function to cause index invalidation , After modification, the execution time is still more than half an hour , open trace Log view gnode Layer log visible join The number of returned rows is much larger than the data volume of the two source tables , So doubt is a Cartesian product problem .

img


The validation found that the two tables have a large number of duplicate values , As shown in the figure below

img

Exists The syntax is applicable to the scene of Association de duplication . Rewrite the associated field to exists After writing ,SQL Can be in 1 Results in minutes
insert into tableXXX
select distinct ct.USER_ID from datumdb.dwa_grp_mem_info_d_20211031 dgmid
where exists (select 1 from datumdb.dwa_grp_mem_use_info_m_202110 ct where ct.GROUP_ID=dgmid.GROUP_ID)




Refer to the answer 2:

版权声明
本文为[CSDN Q & A]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/12/202112121740428096.html

随机推荐