批量update 修改为insert select至临时表，然后做关联update

47条 update语句，关联子查询SQL进行列更新。

优化前SQL的执行耗时分别为： 

00:00:02.34 

00:00:02.35 

00:00:04.17 

00:00:03.88 

00:00:02.78 

00:00:03.64 

00:00:03.78 

00:00:03.80 

00:00:03.94 

00:00:04.71 

00:00:03.91 

00:00:04.50 

00:00:03.86 

00:00:03.19 

00:00:03.59 

00:00:04.62 

00:00:03.44 

00:00:04.35 

00:00:05.87 

00:00:05.44 

00:00:03.52 

00:00:04.02 

00:00:05.36 

00:00:04.22 

00:00:03.86 

00:00:08.16 

00:00:15.21 

00:00:10.65 

00:00:07.48 

00:00:04.18 

00:00:00.85 

00:00:03.78

00:00:00.80 

00:00:04.41 

00:00:04.25 

00:00:04.49 

00:00:03.76 

00:00:04.91 

00:00:00.83 

00:00:00.83 

00:00:04.27 

00:00:05.82 

00:00:04.54 

00:00:06.10 

00:00:04.80 

总计耗时约为3分16秒。

首先，GBase8a数据库是列存数据库，子查询中避免select * 查询出所有列增加额外列的磁盘读写开销，需要查询的列才做select 操作。

进一步的优化，将47步select子查询整合成一张表，然后与目标表mh_10tag_db_cust_corp做关联update。

步骤1：新建hash分布键与目标表一致的临时表，获取需要更新的所有数据结果集，
creaet table mh_10tag_db_cust_corp_temp distributed by ('custno')
as
select t1.custno,
max(case when t1.PROD_NO='01010001' then '是' else '' end ) TAG_500,
max(case when t1.PROD_NO='01010007' then '是' else '' end ) TAG_501,
max(case when t1.PROD_NO='01010008' then '是' else '' end ) TAG_502,
max(case when t1.PROD_NO='01010009' then '是' else '' end ) TAG_503,
max(case when t1.PROD_NO='01010010' then '是' else '' end ) TAG_504,
max(case when t1.PROD_NO='01010011' then '是' else '' end ) TAG_505,
max(case when t1.PROD_NO='01010012' then '是' else '' end ) TAG_506,
max(case when t1.PROD_NO='01010013' then '是' else '' end ) TAG_507,
max(case when t1.PROD_NO='01020001' then '是' else '' end ) TAG_508,
max(case when t1.PROD_NO='01020002' then '是' else '' end ) TAG_509,
max(case when t1.PROD_NO='01020003' then '是' else '' end ) TAG_510,
max(case when t1.PROD_NO='01020004' then '是' else '' end ) TAG_511,
max(case when t1.PROD_NO='01020005' then '是' else '' end ) TAG_512,
max(case when t1.PROD_NO='01020006' then '是' else '' end ) TAG_513,
max(case when t1.PROD_NO='01030001' then '是' else '' end ) TAG_514,
max(case when t1.PROD_NO='01040001' then '是' else '' end ) TAG_515,
max(case when t1.PROD_NO='02010001' then '是' else '' end ) TAG_516,
max(case when t1.PROD_NO='02010002' then '是' else '' end ) TAG_517,
max(case when t1.PROD_NO='02010003' then '是' else '' end ) TAG_518,
max(case when t1.PROD_NO='02020001' then '是' else '' end ) TAG_519,
max(case when t1.PROD_NO='02030001' then '是' else '' end ) TAG_520,
max(case when t1.PROD_NO='02040001' then '是' else '' end ) TAG_521,
max(case when t1.PROD_NO='02040002' then '是' else '' end ) TAG_522,
max(case when t1.PROD_NO='02040003' then '是' else '' end ) TAG_523,
max(case when t1.PROD_NO='02040004' then '是' else '' end ) TAG_524,
max(case when t1.PROD_NO='02050001' then '是' else '' end ) TAG_525,
max(case when t1.PROD_NO='02050002' then '是' else '' end ) TAG_526,
max(case when t1.PROD_NO='02050003' then '是' else '' end ) TAG_527,
max(case when t1.PROD_NO='02050004' then '是' else '' end ) TAG_528,
max(case when t1.PROD_NO='02050005' then '是' else '' end ) TAG_529,
max(case when t1.PROD_NO='02050006' then '是' else '' end ) TAG_530,
max(case when t1.PROD_NO='02050007' then '是' else '' end ) TAG_531,
max(case when t1.PROD_NO='02050008' then '是' else '' end ) TAG_532,
max(case when t1.PROD_NO='02050009' then '是' else '' end ) TAG_533,
max(case when t1.PROD_NO='02060001' then '是' else '' end ) TAG_534,
max(case when t1.PROD_NO='02060002' then '是' else '' end ) TAG_535,
max(case when t1.PROD_NO='02060003' then '是' else '' end ) TAG_536,
max(case when t1.PROD_NO='02060004' then '是' else '' end ) TAG_537,
max(case when t1.PROD_NO='02060005' then '是' else '' end ) TAG_538,
max(case when t1.PROD_NO='02070001' then '是' else '' end ) TAG_539,
max(case when t1.PROD_NO='02070002' then '是' else '' end ) TAG_540,
max(case when t1.PROD_NO='02080001' then '是' else '' end ) TAG_541,
max(case when t1.PROD_NO='02080002' then '是' else '' end ) TAG_542,
max(case when t1.PROD_NO='02080003' then '是' else '' end ) TAG_543,
max(case when t1.PROD_NO='02080004' then '是' else '' end ) TAG_544,
max(case when t1.PROD_NO='02080005' then '是' else '' end ) TAG_545,
max(case when t1.PROD_NO='02080006' then '是' else '' end ) TAG_546,
from mh_10tag_corp_cust_hodg_prod
(SELECT PROD_NO,max(dat) max_dat FROM mh_10tag_corp_cust_hodg_prod
WHERE ifar_org = '10H999' AND HODG_IND = 'Y' group by PROD_NO) t2 on t1.PROD_NO=t2.PROD_NO and t1.dat=t2.max_dat
group by t1.custno;

步骤2：两表做关联update
update mh_10tag_db_cust_corp a
inner join mh_10tag_db_cust_corp_temp t3
on a.custno=t3.custno
a.TAG_500 = t3.TAG_500,
a.TAG_501 = t3.TAG_501,
a.TAG_502 = t3.TAG_502,
a.TAG_503 = t3.TAG_503,
a.TAG_504 = t3.TAG_504,
a.TAG_505 = t3.TAG_505,
a.TAG_506 = t3.TAG_506,
a.TAG_507 = t3.TAG_507,
a.TAG_508 = t3.TAG_508,
a.TAG_509 = t3.TAG_509,
a.TAG_510 = t3.TAG_510,
a.TAG_511 = t3.TAG_511,
a.TAG_512 = t3.TAG_512,
a.TAG_513 = t3.TAG_513,
a.TAG_514 = t3.TAG_514,
a.TAG_515 = t3.TAG_515,
a.TAG_516 = t3.TAG_516,
a.TAG_517 = t3.TAG_517,
a.TAG_518 = t3.TAG_518,
a.TAG_519 = t3.TAG_519,
a.TAG_520 = t3.TAG_520,
a.TAG_521 = t3.TAG_521,
a.TAG_522 = t3.TAG_522,
a.TAG_523 = t3.TAG_523,
a.TAG_524 = t3.TAG_524,
a.TAG_525 = t3.TAG_525,
a.TAG_526 = t3.TAG_526,
a.TAG_527 = t3.TAG_527,
a.TAG_528 = t3.TAG_528,
a.TAG_529 = t3.TAG_529,
a.TAG_530 = t3.TAG_530,
a.TAG_531 = t3.TAG_531,
a.TAG_532 = t3.TAG_532,
a.TAG_533 = t3.TAG_533,
a.TAG_534 = t3.TAG_534,
a.TAG_535 = t3.TAG_535,
a.TAG_536 = t3.TAG_536,
a.TAG_537 = t3.TAG_537,
a.TAG_538 = t3.TAG_538,
a.TAG_539 = t3.TAG_539,
a.TAG_540 = t3.TAG_540,
a.TAG_541 = t3.TAG_541,
a.TAG_542 = t3.TAG_542,
a.TAG_543 = t3.TAG_543,
a.TAG_544 = t3.TAG_544,
a.TAG_545 = t3.TAG_545,
a.TAG_546 = t3.TAG_546;

其中步骤一insert select 到临时表耗时29秒，关联update耗时3秒。
优化结论：性能提升6倍。