15002协议处理优化

1.问题描述与分析

从日志中发现通用协议15002处理经常比较耗时,其中数据库查询明显耗时过高.

以下是一例:

select Max(tb_10068_1050.object_id) as MaxNo,Count(*) as SumNo from  tb_10068_1050
left join (Select MAX(f003v_1045) as f003v_1045,MAX(f002v_1045) as f002v_1045,f004v_1045,MAX(f005v_1045) as f005v_1045 from tb_10068_1045 group by f004v_1045) tb_10068_1045  
on TB_10068_1050.F011V_1050 = TB_10068_1045.F004V_1045


left join (select Ref_objectid,status from tb_1049 where sheet_type = 100681050 ) tb_1049  
on TB_10068_1050.OBJECT_ID = TB_1049.REF_OBJECTID

left join  vv_1061
on ((tb_10068_1050.src_orgid = vv_1061.orgid and tb_10068_1050.dest_orgid = vv_1061.co_orgid     and vv_1061.userSerial = 100681145) or (tb_10068_1050.src_orgid = vv_1061.co_orgid and tb_10068_1050.dest_orgid = vv_1061.orgid and vv_1061.userSerial = 100681145))    

where (tb_10068_1050.f028v_1050 = 1 or tb_10068_1050.f005d_1050 >=  '2013-11-21')  

and ( dest_orgid = '' or dest_orgid is null or co_orgid is not null ) 

此查询在JJY环境下耗时12秒.用数据库引擎优化顾问优化后性能几乎没有任何提升.

如果去掉最后面的:
and ( dest_orgid = '' or dest_orgid is null or co_orgid is not null )
则查询几乎没有耗时.

以下从SQL角度进行粗略分析,目的是缩短请求的处理时间.

数据量:

相关表的数据记录数:

select count(*) from tb_1049; --1220

select count(*) from tb_10068_1045; --628

select count(*) from tb_10068_1050; --242089

select count(*) from vv_1061; --116010

select count(*) from vv_1061 where co_orgid is  null; ---0

把上述条件替换为以下条件的测试结果如下:
(1)and (co_orgid is not null or dest_orgid=0); ---耗时12秒
(2)and (vv_1061.co_orgid is not null); ---耗时:0
(3)and (tb_10068_1050.dest_orgid=''); ---耗时:0

(1)是(2)和(3)组合起来的条件.

2个条件单独使用很快，用OR组合后就很慢,如何解释？

select Max(tb_10068_1050.object_id) as MaxNo,Count(*) as SumNo from  tb_10068_1050

left join (Select MAX(f003v_1045) as f003v_1045,MAX(f002v_1045) as f002v_1045,f004v_1045,MAX(f005v_1045) as f005v_1045 from tb_10068_1045 group by f004v_1045) tb_10068_1045  
on TB_10068_1050.F011V_1050 = TB_10068_1045.F004V_1045

-- left join (select Ref_objectid,status from tb_1049 where sheet_type = 100681050 ) tb_1049  
-- on TB_10068_1050.OBJECT_ID = TB_1049.REF_OBJECTID

left join  vv_1061
on ((tb_10068_1050.src_orgid = vv_1061.orgid and tb_10068_1050.dest_orgid = vv_1061.co_orgid     and vv_1061.userSerial = 100681145) or (tb_10068_1050.src_orgid = vv_1061.co_orgid and tb_10068_1050.dest_orgid = vv_1061.orgid and vv_1061.userSerial = 100681145))    

where (tb_10068_1050.f028v_1050 = 1 or tb_10068_1050.f005d_1050 >=  '2013-11-21')  
and (co_orgid is not null or dest_orgid=0)

执行上面注释部分语句(tb_1049)的命令,则上述命令没有什么耗时.
tb_1049只有1220条记录,TB_10068_1050.OBJECT_ID = TB_1049.REF_OBJECTID都有索引关联. 这个现象如何解释?

也许只有分析数据库的查询执行计划才知其所以.----这里不细究了.

条件 "( dest_orgid = '' or dest_orgid is null or co_orgid is not null ) "的含义?

(1)可以查询没有目标机构的单据(dest_orgid = '' or dest_orgid is null)：即有的单据可以没有目标机构,不需要建立合作关系.

(2)如果有目标机构信息,需要有管理权限(co_orgid is not null)。

如何描述可以没有目标机构信息呢?

利用单据信息表的单据属性字段,目前的定义为:

f019n_0044    单据属性    int(1)            0    单据属性bit-0:是否在手机上显示bit1-是否支持汇总

对其进行扩展: bit2-是否允许没有目标机构,默认:0,表示不允许,1-表示允许
注意:不能使用是否自动发送字段(f012n_0044),该字段用于有目标机构但不需要发送的情况.

ISheetType接口中有关于单据属性的访问方法

    virtual long get_prop() const = 0;
    virtual void set_prop(long flag) = 0;
    virtual void clr_prop(long flag) = 0;

2.优化处理

15002协议实现调整。

.优化SQL命令:

如下:

select Max(tb_10068_1050.object_id) as MaxNo,Count(*) as SumNo from  tb_10068_1050
left join  vv_1061
on ((tb_10068_1050.src_orgid = vv_1061.orgid and tb_10068_1050.dest_orgid = vv_1061.co_orgid     and vv_1061.userSerial = 100681145) or (tb_10068_1050.src_orgid = vv_1061.co_orgid and tb_10068_1050.dest_orgid = vv_1061.orgid and vv_1061.userSerial = 100681145))    

where (tb_10068_1050.f028v_1050 = 1 or tb_10068_1050.f005d_1050 >=  '2013-11-21')  

and (附加条件)

.附加条件的生成,利用单据属性控制查询条件

ISheetInfo *sti = ibiz_->GetSheetType(sheet_type);
string strWhere; ///< 附加条件
if (get_prop()&0x4) { ///< 允许没有dest_orgid
    strWhere = "dest_orgid = '' or dest_orgid is null or co_orgid is not null";
    ///< 可以约定dest_orgid默认为0,表示无目标机构,可以简化为
    ///< strWhere = "dest_orgid = 0 or co_orgid is not null";
}
else {
    strWhere = "co_orgid is not null";
}

该查询是为后续查询准备,提供分页查询信息。实际查询时再带上tb_10068_1045和tb_1049的信息。

升级时需要更新tb_0044:

对于允许没有目标机构的单据设置属性.

例如, 设置邀约单单据属性为可以没有合作机构

update tb_0044 set f019n_0044=f019n_0044+4 where sheet_type=邀约单