现有4张表: memberMc，member，customer，mc(每张表都有1000万数据)
memberMc表上建有如下索引: 1. (company_code,member_id,frame_no,engine_no)
2. (company_code,dealer_code)
3. (company_code,member_id)
4. (company_code,frame_no,engine_no)
customer表上建有如下索引: 1. (company_code,member_id)
2. (company_code,dealer_code)
member表上建有如下索引: 1. (company_code,member_id)
2. (company_code,dealer_code)
3. (company_code,id_number)
mc表上建有如下索引: 1. (company_code,frame_no,engine_no)
2. (company_code,dealer_code)
现用以下这段SQL文检索总件数时，用了25min左右.(结果集:9万左右)
SQL: select count( *)
from IC_MEMBER_MC this_
inner join IC_CUSTOMER ct3_ on
this_.COMPANY_CODE = ct3_.COMPANY_CODE
and this_.MEMBER_ID = ct3_.MEMBER_ID
inner join IC_MEMBER mb4_ on
this_.COMPANY_CODE = mb4_.COMPANY_CODE
and this_.MEMBER_ID = mb4_.MEMBER_ID
inner join IC_MC mc1_ on
mc1_.COMPANY_CODE = this_.COMPANY_CODE
and mc1_.FRAME_NO = this_.FRAME_NO
and mc1_.ENGINE_NO = this_.ENGINE_NO
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
也作了如下的测试:
1. 主表统计
select count(*)
from IC_MEMBER_MC this_
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:55s
2. memberMc与customer join
select count(*)
from IC_MEMBER_MC this_
inner join IC_CUSTOMER ct3_ on
this_.COMPANY_CODE = ct3_.COMPANY_CODE
and this_.MEMBER_ID = ct3_.MEMBER_ID
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:6min28s
3. memberMc与member join
select count(*)
from IC_MEMBER_MC this_
inner join IC_MEMBER mb4_ on
this_.COMPANY_CODE = mb4_.COMPANY_CODE
and this_.MEMBER_ID = mb4_.MEMBER_ID
where this_.COMPANY_CODE="6548"
and this_.DEALER_CODE="JA0013"
时间:7min, 2表外连接的时间也是7min.
4. memberMc,customer,member
时间:15min
5. memberMc,customer,member(left join)
时间:15min
------解决方案--------------------------------------------------------
外键连接做count效率较低，如果采用ec方式查看sqlcode.sqlerrd[2]的值可以直接获取到处理的记录数，或者先预处理维度表，尽量减少关联数据表的数量后，再做外键关联统计，总之如果做好提数分析及效率是需要花功夫测试，通常很费时间的^_^
------解决方案--------------------------------------------------------
informix 在join动作，需要数据库的pk，fk配合使用，楼主检查下company_code是不是可以设置为pk，另外可以对有没有对索引相关字段做统计更新？？
还有表格上也是需要做统计更新
------解决方案--------------------------------------------------------
1.外键连表效率较低
2.可以在每个连接的查询语句中加入些限制条件性语句吗？比如我看好像每个表都有company_code字段。
总之，要想速度快，限制条件就得多。