为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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))
1、检查一下应用端驱动版本
2、是否是绑定变量传参,可以对比一下传参和不传参的执行计划