注册

达梦查询语句优化,请社区各位达梦老师指导,诚心感谢!

阿杰升级路 2024/05/23 1226 34

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:win10
【CPU】:
【问题描述】*:现在有一个sql语句,在我本地查询时4000多行用时0.2秒,147000行用时3秒,感觉还能接受;但是在局域网内别人连我的库相同的语句查询4000多行需要16秒多,左下角那个行数就是200-800的变化,查询147000多更费劲。

这是怎么回事?我应该从哪里排查?

执行计划


1   #NSET2: [63, 2690, 2023] 
2     #LOCAL COLLECT: [63, 2690, 2023]; op_id(5) n_grp_by (0) n_cols(0) n_keys(0) for_sync(FALSE)
3       #PRJT2: [63, 2690, 2023]; exp_num(40), is_atom(FALSE) 
4         #HASH RIGHT JOIN2: [63, 2690, 2023]; key_num(1), ret_null(0), KEY(a.AREA_CODE=mip.AreaCode)
5           #LOCAL SCATTER: [1, 45, 52]; op_id(4) notify_only(FALSE) 
6             #NTTS2: [1, 45, 52]; for_mdis(FALSE); is_atom(FALSE)
7               #LOCAL GATHER: [1, 45, 52]; op_id(3) n_grp_by (0) n_cols(0) n_keys(0) invoke_flag(0) top_flag(0)
8                 #CSCN2: [1, 45, 52]; INDEX33555511(SYS_AREA as a); btr_scan(1)
9           #INDEX JOIN LEFT JOIN2: [61, 2271, 2023]  ret_null(0)
10            #HASH2 INNER JOIN: [51, 2271, 2023];  KEY_NUM(3); KEY(mtp.YEAR=mip.Year AND mtp.BATCH=mip.Batch AND mtp.JiJFDBH=mip.FDCode) KEY_NULL_EQU(0, 0, 0)
11              #LOCAL SCATTER: [13, 3, 1104]; op_id(2) notify_only(FALSE) 
12                #NTTS2: [13, 3, 1104]; for_mdis(FALSE); is_atom(FALSE)
13                  #LOCAL GATHER: [13, 3, 1104]; op_id(1) n_grp_by (0) n_cols(0) n_keys(0) invoke_flag(0) top_flag(0)
14                    #PRJT2: [13, 3, 1104]; exp_num(10), is_atom(FALSE) 
15                      #SLCT2: [13, 3, 1104]; (pm.Year = dl.Year AND pm.Batch = dl.Batch AND dl.Year = xl.Year AND dl.Batch = xl.Batch)
16                        #NEST LOOP INDEX JOIN2: [13, 3, 1104] 
17                          #SLCT2: [13, 3, 996]; (exp11 = 4 AND xl.Year = pm.Year AND xl.Batch = pm.Batch)
18                            #NEST LOOP INDEX JOIN2: [13, 3, 996] 
19                              #SLCT2: [13, 3, 888]; exp11 = 6
20                                #NEST LOOP INDEX JOIN2: [13, 3, 888] 
21                                  #SLCT2: [12, 3, 768]; (exp11 = 0 AND exp11 = 8)
22                                    #BLKUP2: [12, 5977, 768]; DL_XL_PM_FD(fd)
23                                      #SSEK2: [12, 5977, 768]; scan_type(ASC), DL_XL_PM_FD(TD_MACHINE_TYPE_PUSH as fd), scan_range[(11,min,min,min),(11,max,max,max))
24                                  #BLKUP2: [1, 1, 60]; DL_XL_PM_FD(pm)
25                                    #SSEK2: [1, 1, 60]; scan_type(ASC), DL_XL_PM_FD(TD_MACHINE_TYPE_PUSH as pm), scan_range[(11,fd.Year,fd.Batch,exp11),(11,fd.Year,fd.Batch,exp11)]
26                              #BLKUP2: [1, 1, 60]; DL_XL_PM_FD(xl)
27                                #SSEK2: [1, 1, 60]; scan_type(ASC), DL_XL_PM_FD(TD_MACHINE_TYPE_PUSH as xl), scan_range[(11,fd.Year,fd.Batch,exp11),(11,fd.Year,fd.Batch,exp11)]
28                          #BLKUP2: [1, 1, 60]; DL_XL_PM_FD(dl)
29                            #SSEK2: [1, 1, 60]; scan_type(ASC), DL_XL_PM_FD(TD_MACHINE_TYPE_PUSH as dl), scan_range[(11,fd.Year,fd.Batch,exp11),(11,fd.Year,fd.Batch,exp11)]
30              #BLKUP2: [34, 14593, 919]; AreaCode_State_Yea_Batch_AprvBatch(mip)
31                #SSEK2: [34, 14593, 919]; scan_type(ASC), AreaCode_State_Yea_Batch_AprvBatch(TD_MACHINE_PUSH as mip), scan_range[(11,min,min,min,min),(11,max,max,max,max))
32            #SSEK2: [10, 1, 100]; scan_type(ASC), FacId_AreaCode_Name(TD_FACPUSH as fp), scan_range[(mip.FacId,mip.AreaCode,min),(mip.FacId,mip.AreaCode,max))
回答 0
暂无回答
扫一扫
联系客服