String sql = "
select c.BRANCH_CODE || '-' || a.RES_DETAIL_NAME as BRANCH_CODE_NAME,
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
from ci_idms_basic_master_cmp_log c
left join (select distinct t.RES_DETAIL_VALUE, t.RES_DETAIL_NAME
from view_ci_auth_role_resource t
where role_id in (select role_id
from view_ci_auth_role_user
where user_id = 'admin')
and t.RES_ID = '53') a on a.RES_DETAIL_VALUE =
c.branch_code
left join ci_sys_sov_member dic7 on c.COUNTRY = dic7.member_value
and dic7.group_code = 'IX_COUNTRY'
and dic7.sys_type = 'IDMS'
and dic7.branch_code = '0912'
left join ci_sys_sov_member dic38 on c.WWIC = dic38.member_value
and dic38.group_code = 'IX_WWIC'
and dic38.sys_type = 'IDMS'
and dic38.branch_code = '0912'
left join ci_sys_sov_member dic11 on c.LEGAL_STATUS = dic11.member_value
and dic11.group_code =
'IX_LEGAL_STATUS'
and dic11.sys_type = 'IDMS'
and dic11.branch_code = '0912'
left join ci_sys_sov_member dic15 on c.RESIDENT_CODE =
dic15.member_value
and dic15.group_code =
'IX_RESIDENT_CODE'
and dic15.sys_type = 'IDMS'
and dic15.branch_code = '0912'
left join ci_sys_sov_member dic12 on c.PROF_BUSINESS =
dic12.member_value
and dic12.group_code =
'IX_PROF_BUSINESS'
and dic12.sys_type = 'IDMS'
and dic12.branch_code = '0912'
left join ci_sys_sov_member dic16 on c.GROUP_CODE = dic16.member_value
and dic16.group_code = 'IX_GROUP_CODE'
and dic16.sys_type = 'IDMS'
and dic16.branch_code = '0912'
left join ci_sys_sov_member dic13 on c.ADVISOR_CODE = dic13.member_value
and dic13.group_code =
'IX_ADVISOR_CODE'
and dic13.sys_type = 'IDMS'
and dic13.branch_code = '0912'
left join ci_sys_sov_member dic14 on c.COUNTRY_CODE = dic14.member_value
and dic14.group_code = 'IX_COUNTRY'
and dic14.sys_type = 'IDMS'
and dic14.branch_code = '0912'
left join ci_sys_sov_member dic18 on c.KWG_REP_INDICATOR =
dic18.member_value
and dic18.group_code =
'ALL_KWG_REP_INDICATOR'
and dic18.sys_type = 'IDMS'
and dic18.branch_code = '0912'
left join ci_sys_sov_member dic21 on c.HO_PROF_BUSINESS_CODE =
dic21.member_value
and dic21.group_code =
'IX_HO_PROF_BUSINESS'
and dic21.sys_type = 'IDMS'
and dic21.branch_code = '0912'
left join ci_sys_sov_member dic19 on c.BANK_CODE = dic19.member_value
and dic19.group_code = 'IX_BANK_CODE'
and dic19.sys_type = 'IDMS'
and dic19.branch_code = '0912'
left join ci_sys_sov_member dic23 on c.MNC_CODE = dic23.member_value
and dic23.group_code = 'IX_MNC_CODE'
and dic23.sys_type = 'IDMS'
and dic23.branch_code = '0912'
left join ci_sys_sov_member dic24 on c.OWNERSHIP_OF_MNC =
dic24.member_value
and dic24.group_code = 'IX_COUNTRY'
and dic24.sys_type = 'IDMS'
and dic24.branch_code = '0912'
left join ci_sys_sov_member dic25 on c.SIZE_CLASSIFICATION =
dic25.member_value
and dic25.group_code =
'IX_SIZE_CLASSIFICATION'
and dic25.sys_type = 'IDMS'
and dic25.branch_code = '0912'
left join ci_sys_sov_member dic53 on c.PRIMARY_CUSTOMER =
dic53.member_value
and dic53.group_code = 'ALL_YES_NO'
and dic53.sys_type = 'IDMS'
and dic53.branch_code = '0912'
left join ci_sys_sov_member dic55 on c.P1G_ZONE = dic55.member_value
and dic55.group_code = 'ALL_A_B'
and dic55.sys_type = 'IDMS'
and dic55.branch_code = '0912'
left join ci_sys_sov_member dic56 on c.COUNTERPARTY_CLASS =
dic56.member_value
and dic56.group_code =
'IX_COUNTERPARTY_CLASS'
and dic56.sys_type = 'IDMS'
and dic56.branch_code = '0912'
left join ci_sys_sov_member dic41 on c.SPECIAL_SPECIAL_IND =
dic41.member_value
and dic41.group_code = 'ALL_YES_NO'
and dic41.sys_type = 'IDMS'
and dic41.branch_code = '0912'
left join ci_sys_sov_member dic45 on c.EX_RATE_FOR_TXNS =
dic45.member_value
and dic45.group_code = 'ALL_S_M'
and dic45.sys_type = 'IDMS'
and dic45.branch_code = '0912'
left join ci_sys_sov_member dic46 on c.EX_RATE_FOR_CHARGES =
dic46.member_value
and dic46.group_code = 'ALL_S_M'
and dic46.sys_type = 'IDMS'
and dic46.branch_code = '0912'
left join ci_sys_sov_member dic44 on c.WAIVE_CHARGE = dic44.member_value
and dic44.group_code = 'ALL_YES_NO'
and dic44.sys_type = 'IDMS'
and dic44.branch_code = '0912'
left join ci_sys_sov_member dic49 on c.STRAIGHT_THRU_PROCESS =
dic49.member_value
and dic49.group_code = 'ALL_YES_NO_E'
and dic49.sys_type = 'IDMS'
and dic49.branch_code = '0912'
where 1 = 1
and c.branch_code = '0912'
order by c.master_number, c.REVISION desc
";
List list = jdbcTemplate.queryForList(sql,ps.toArray());
以上SQL,在本地跑了10多分钟。 其实总共就6000多条数据。 数据量也不大呀,为什么跑这么长的时间呀。
select c.BRANCH_CODE || '-' || a.RES_DETAIL_NAME as BRANCH_CODE_NAME,
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
from ci_idms_basic_master_cmp_log c
left join (select distinct t.RES_DETAIL_VALUE, t.RES_DETAIL_NAME
from view_ci_auth_role_resource t
where role_id in (select role_id
from view_ci_auth_role_user
where user_id = 'admin')
and t.RES_ID = '53') a on a.RES_DETAIL_VALUE =
c.branch_code
left join ci_sys_sov_member dic7 on c.COUNTRY = dic7.member_value
and dic7.group_code = 'IX_COUNTRY'
and dic7.sys_type = 'IDMS'
and dic7.branch_code = '0912'
left join ci_sys_sov_member dic38 on c.WWIC = dic38.member_value
and dic38.group_code = 'IX_WWIC'
and dic38.sys_type = 'IDMS'
and dic38.branch_code = '0912'
left join ci_sys_sov_member dic11 on c.LEGAL_STATUS = dic11.member_value
and dic11.group_code =
'IX_LEGAL_STATUS'
and dic11.sys_type = 'IDMS'
and dic11.branch_code = '0912'
left join ci_sys_sov_member dic15 on c.RESIDENT_CODE =
dic15.member_value
and dic15.group_code =
'IX_RESIDENT_CODE'
and dic15.sys_type = 'IDMS'
and dic15.branch_code = '0912'
left join ci_sys_sov_member dic12 on c.PROF_BUSINESS =
dic12.member_value
and dic12.group_code =
'IX_PROF_BUSINESS'
and dic12.sys_type = 'IDMS'
and dic12.branch_code = '0912'
left join ci_sys_sov_member dic16 on c.GROUP_CODE = dic16.member_value
and dic16.group_code = 'IX_GROUP_CODE'
and dic16.sys_type = 'IDMS'
and dic16.branch_code = '0912'
left join ci_sys_sov_member dic13 on c.ADVISOR_CODE = dic13.member_value
and dic13.group_code =
'IX_ADVISOR_CODE'
and dic13.sys_type = 'IDMS'
and dic13.branch_code = '0912'
left join ci_sys_sov_member dic14 on c.COUNTRY_CODE = dic14.member_value
and dic14.group_code = 'IX_COUNTRY'
and dic14.sys_type = 'IDMS'
and dic14.branch_code = '0912'
left join ci_sys_sov_member dic18 on c.KWG_REP_INDICATOR =
dic18.member_value
and dic18.group_code =
'ALL_KWG_REP_INDICATOR'
and dic18.sys_type = 'IDMS'
and dic18.branch_code = '0912'
left join ci_sys_sov_member dic21 on c.HO_PROF_BUSINESS_CODE =
dic21.member_value
and dic21.group_code =
'IX_HO_PROF_BUSINESS'
and dic21.sys_type = 'IDMS'
and dic21.branch_code = '0912'
left join ci_sys_sov_member dic19 on c.BANK_CODE = dic19.member_value
and dic19.group_code = 'IX_BANK_CODE'
and dic19.sys_type = 'IDMS'
and dic19.branch_code = '0912'
left join ci_sys_sov_member dic23 on c.MNC_CODE = dic23.member_value
and dic23.group_code = 'IX_MNC_CODE'
and dic23.sys_type = 'IDMS'
and dic23.branch_code = '0912'
left join ci_sys_sov_member dic24 on c.OWNERSHIP_OF_MNC =
dic24.member_value
and dic24.group_code = 'IX_COUNTRY'
and dic24.sys_type = 'IDMS'
and dic24.branch_code = '0912'
left join ci_sys_sov_member dic25 on c.SIZE_CLASSIFICATION =
dic25.member_value
and dic25.group_code =
'IX_SIZE_CLASSIFICATION'
and dic25.sys_type = 'IDMS'
and dic25.branch_code = '0912'
left join ci_sys_sov_member dic53 on c.PRIMARY_CUSTOMER =
dic53.member_value
and dic53.group_code = 'ALL_YES_NO'
and dic53.sys_type = 'IDMS'
and dic53.branch_code = '0912'
left join ci_sys_sov_member dic55 on c.P1G_ZONE = dic55.member_value
and dic55.group_code = 'ALL_A_B'
and dic55.sys_type = 'IDMS'
and dic55.branch_code = '0912'
left join ci_sys_sov_member dic56 on c.COUNTERPARTY_CLASS =
dic56.member_value
and dic56.group_code =
'IX_COUNTERPARTY_CLASS'
and dic56.sys_type = 'IDMS'
and dic56.branch_code = '0912'
left join ci_sys_sov_member dic41 on c.SPECIAL_SPECIAL_IND =
dic41.member_value
and dic41.group_code = 'ALL_YES_NO'
and dic41.sys_type = 'IDMS'
and dic41.branch_code = '0912'
left join ci_sys_sov_member dic45 on c.EX_RATE_FOR_TXNS =
dic45.member_value
and dic45.group_code = 'ALL_S_M'
and dic45.sys_type = 'IDMS'
and dic45.branch_code = '0912'
left join ci_sys_sov_member dic46 on c.EX_RATE_FOR_CHARGES =
dic46.member_value
and dic46.group_code = 'ALL_S_M'
and dic46.sys_type = 'IDMS'
and dic46.branch_code = '0912'
left join ci_sys_sov_member dic44 on c.WAIVE_CHARGE = dic44.member_value
and dic44.group_code = 'ALL_YES_NO'
and dic44.sys_type = 'IDMS'
and dic44.branch_code = '0912'
left join ci_sys_sov_member dic49 on c.STRAIGHT_THRU_PROCESS =
dic49.member_value
and dic49.group_code = 'ALL_YES_NO_E'
and dic49.sys_type = 'IDMS'
and dic49.branch_code = '0912'
where 1 = 1
and c.branch_code = '0912'
order by c.master_number, c.REVISION desc
";
List list = jdbcTemplate.queryForList(sql,ps.toArray());
以上SQL,在本地跑了10多分钟。 其实总共就6000多条数据。 数据量也不大呀,为什么跑这么长的时间呀。
解决方案 »
- 当类里执行修改数据库的时候
- applet中怎么弹出广告IE窗口?
- weblogic连接oracle7,在执行查询语句时奇怪的问题.
- 急盼答复
- 新手,如果用jsp+javabean+servlet+struts+Hibernate这些知识做了一电子商务网站?
- 请最最最高手帮忙解决最最最困难的问题,小弟不胜感激,将赠200分(关于在jsp中服务端向客户端传送文件)
- 做个小调查: 大家平常用resin作服务器还是tomcat?
- 在java.util.*下面有一个这个类:ResourceBundle,请问这个类是什么意思?怎么用?有什么好处?
- 一个form中有几个按钮,怎样判断用户是选择的那个呢...比如有注册,登陆,忘记密码...
- JAVA web tomcat 服务器无法启动问题
- 今天重装系统把c盘格式化了,然后就装不上了,报错如下
- jquery 代码实现input 显示值问题
1:建索引、不多说、百度上一大堆
2:先加条件、在查询,也就是说先判断下、把那些没用的数据都排除了,然后在left join补充一下、left join 有时候运行的很慢的、上次我数据库里也就2000条数据,另一个表中数据不多,left join 一次要花上10+秒、用了上面两个方法在执行只花0.01秒不到
left join 在俩表数据量差很多的时候会执行的很慢的
其实这段SQL主要是为EXCEL导出用的,
可是发现就单跑这段sql用的时间就太吓人了。
不要再说下面的excel导出。
其实在PL/SQL里面跑的话,数据也出来的挺快的。
但是用java这段代码跑出来,时间实在是太吓人了。
List list = jdbcTemplate.queryForList(sql,ps.toArray());
但他门的系统为何跑得这么快, 我想他门不会这样写SQL的,
加上分流,分表,他门的SYSTEM就跑得很快,
他门有专人处理的, 如果真的要连两个大到连不起的表,
我想他门会用别的方法, 不是用SQL来做
还是重新改一下你表结构吧
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
FROM ci_idms_basic_master_cmp_log c
LEFT JOIN (SELECT DISTINCT t.RES_DETAIL_VALUE, t.RES_DETAIL_NAME
FROM view_ci_auth_role_resource t
WHERE role_id IN (SELECT role_id
FROM view_ci_auth_role_user
WHERE user_id = 'admin')
AND t.RES_ID = '53') a
ON a.RES_DETAIL_VALUE = c.branch_code
LEFT JOIN ci_sys_sov_member dic7
ON c.COUNTRY = dic7.member_value
AND dic7.group_code = 'IX_COUNTRY'
AND dic7.sys_type = 'IDMS'
AND dic7.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic38
ON c.WWIC = dic38.member_value
AND dic38.group_code = 'IX_WWIC'
AND dic38.sys_type = 'IDMS'
AND dic38.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic11
ON c.LEGAL_STATUS = dic11.member_value
AND dic11.group_code = 'IX_LEGAL_STATUS'
AND dic11.sys_type = 'IDMS'
AND dic11.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic15
ON c.RESIDENT_CODE = dic15.member_value
AND dic15.group_code = 'IX_RESIDENT_CODE'
AND dic15.sys_type = 'IDMS'
AND dic15.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic12
ON c.PROF_BUSINESS = dic12.member_value
AND dic12.group_code = 'IX_PROF_BUSINESS'
AND dic12.sys_type = 'IDMS'
AND dic12.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic16
ON c.GROUP_CODE = dic16.member_value
AND dic16.group_code = 'IX_GROUP_CODE'
AND dic16.sys_type = 'IDMS'
AND dic16.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic13
ON c.ADVISOR_CODE = dic13.member_value
AND dic13.group_code = 'IX_ADVISOR_CODE'
AND dic13.sys_type = 'IDMS'
AND dic13.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic14
ON c.COUNTRY_CODE = dic14.member_value
AND dic14.group_code = 'IX_COUNTRY'
AND dic14.sys_type = 'IDMS'
AND dic14.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic18
ON c.KWG_REP_INDICATOR = dic18.member_value
AND dic18.group_code = 'ALL_KWG_REP_INDICATOR'
AND dic18.sys_type = 'IDMS'
AND dic18.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic21
ON c.HO_PROF_BUSINESS_CODE = dic21.member_value
AND dic21.group_code = 'IX_HO_PROF_BUSINESS'
AND dic21.sys_type = 'IDMS'
AND dic21.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic19
ON c.BANK_CODE = dic19.member_value
AND dic19.group_code = 'IX_BANK_CODE'
AND dic19.sys_type = 'IDMS'
AND dic19.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic23
ON c.MNC_CODE = dic23.member_value
AND dic23.group_code = 'IX_MNC_CODE'
AND dic23.sys_type = 'IDMS'
AND dic23.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic24
ON c.OWNERSHIP_OF_MNC = dic24.member_value
AND dic24.group_code = 'IX_COUNTRY'
AND dic24.sys_type = 'IDMS'
AND dic24.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic25
ON c.SIZE_CLASSIFICATION = dic25.member_value
AND dic25.group_code = 'IX_SIZE_CLASSIFICATION'
AND dic25.sys_type = 'IDMS'
AND dic25.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic53
ON c.PRIMARY_CUSTOMER = dic53.member_value
AND dic53.group_code = 'ALL_YES_NO'
AND dic53.sys_type = 'IDMS'
AND dic53.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic55
ON c.P1G_ZONE = dic55.member_value
AND dic55.group_code = 'ALL_A_B'
AND dic55.sys_type = 'IDMS'
AND dic55.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic56
ON c.COUNTERPARTY_CLASS = dic56.member_value
AND dic56.group_code = 'IX_COUNTERPARTY_CLASS'
AND dic56.sys_type = 'IDMS'
AND dic56.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic41
ON c.SPECIAL_SPECIAL_IND = dic41.member_value
AND dic41.group_code = 'ALL_YES_NO'
AND dic41.sys_type = 'IDMS'
AND dic41.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic45
ON c.EX_RATE_FOR_TXNS =dic45.member_value
AND dic45.group_code = 'ALL_S_M'
AND dic45.sys_type = 'IDMS'
AND dic45.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic46
ON c.EX_RATE_FOR_CHARGES = dic46.member_value
AND dic46.group_code = 'ALL_S_M'
AND dic46.sys_type = 'IDMS'
AND dic46.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic44
ON c.WAIVE_CHARGE = dic44.member_value
AND dic44.group_code = 'ALL_YES_NO'
AND dic44.sys_type = 'IDMS'
AND dic44.branch_code = '0912'
LEFT JOIN ci_sys_sov_member dic49
ON c.STRAIGHT_THRU_PROCESS = dic49.member_value
AND dic49.group_code = 'ALL_YES_NO_E'
AND dic49.sys_type = 'IDMS'
AND dic49.branch_code = '0912'
WHERE 1 = 1
AND c.branch_code = '0912'
ORDER BY c.master_number, c.REVISION DESC
一张表就算1千条数据好了,总记录数:1000的(23-1)次方:1.e+69
这是什么数字呀,1的后面写69个0!!!!!!!!!!!!
这sql估计一年半截也跑不完呀......只能讲明一个问题:笛卡尔积很给力!
SELECT c.BRANCH_CODE || '-' || a.RES_DETAIL_NAME as BRANCH_CODE_NAME,
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
SELECT c.BRANCH_CODE || '-' || a.RES_DETAIL_NAME as BRANCH_CODE_NAME,
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
对于你的实力,你最好直接建立jdbc,速度会快,然后再包装。你这个sql以及你的标题,已经暴露了你,不需要用框架了,用不明白的
A(RES_DETAIL_VALUE, RES_DETAIL_NAME) AS
( SELECT DISTINCT t.RES_DETAIL_VALUE, t.RES_DETAIL_NAME
FROM view_ci_auth_role_resource t
WHERE role_id IN (SELECT role_id
FROM view_ci_auth_role_user
WHERE user_id = 'admin')
AND t.RES_ID = '53'
),dic(group_code, member_value, member_text, group_code, sys_type, branch_code) AS
( SELECT group_code, member_value, member_text, group_code, sys_type, branch_code
FROM ci_sys_sov_member
WHERE sys_type = 'IDMS' AND branch_code = '0912'
),
dic7(member_value, member_text) AS
( SELECT member_value, member_text
FROM dic
WHERE group_code = 'IX_COUNTRY'
),
...log(BRANCH_CODE,...,) AS
( SELECT BRANCH_CODE,...,
FROM ci_idms_basic_master_cmp_log
WHERE branch_code = '0912'
) SELECT c.BRANCH_CODE || '-' || A.RES_DETAIL_NAME as BRANCH_CODE_NAME,
dic7.member_value || '-' || dic7.member_text as COUNTRY_NAME,
dic38.member_value || '-' || dic38.member_text as WWIC_NAME,
dic11.member_value || '-' || dic11.member_text as LEGAL_STATUS_NAME,
dic15.member_value || '-' || dic15.member_text as RESIDENT_CODE_NAME,
dic12.member_value || '-' || dic12.member_text as PROF_BUSINESS_NAME,
dic16.member_value || '-' || dic16.member_text as GROUP_CODE_NAME,
dic13.member_value || '-' || dic13.member_text as ADVISOR_CODE_NAME,
dic14.member_value || '-' || dic14.member_text as COUNTRY_CODE_NAME,
dic18.member_value || '-' || dic18.member_text as KWG_REP_INDICATOR_NAME,
dic21.member_value || '-' || dic21.member_text as HO_PROF_BUSINESS_CODE_NAME,
dic19.member_value || '-' || dic19.member_text as BANK_CODE_NAME,
dic23.member_value || '-' || dic23.member_text as MNC_CODE_NAME,
dic24.member_value || '-' || dic24.member_text as OWNERSHIP_OF_MNC_NAME,
dic25.member_value || '-' || dic25.member_text as SIZE_CLASSIFICATION_NAME,
dic53.member_value || '-' || dic53.member_text as PRIMARY_CUSTOMER_NAME,
c.*
FROM log c
LEFT JOIN A ON A.RES_DETAIL_VALUE = c.branch_code
LEFT JOIN dic7 ON c.COUNTRY = dic7.member_value
...ORDER BY c.master_number, c.REVISION DESC
你这SQL语句 C.*就不是优化过的,还有要确定你作连接的键是否是外键 是否有索引
一 : 看一下下面这个SQL 的执行时间如何。
Select c.*
FROM ci_idms_basic_master_cmp_log c
LEFT JOIN (SELECT DISTINCT t.RES_DETAIL_VALUE, t.RES_DETAIL_NAME
FROM view_ci_auth_role_resource t
WHERE role_id IN (SELECT role_id
FROM view_ci_auth_role_user
WHERE user_id = 'admin')
AND t.RES_ID = '53') a
ON a.RES_DETAIL_VALUE = c.branch_code
where 1=1
and c.branch_code = '0912'
二. ci_sys_sov_member 上建立唯一索引 ( branch_code ,sys_type ,group_code ,member_value )
如果已经索引的话,重建一次试试
三、如果还有问题,尝试加一下hint,强制用ci_idms_basic_master_cmp_log 作为驱动表
我门要看的,他门不让我门看,
可能他门会说,自动连五个大表,你玩啊,
不如我找人挨个挨个人手SELET给你算了。
上面有人说了、
你还是用jdbc吧、
你这点数据不算什么、
我干电信的时候一下子就上千万、
然后写一千行sql进行统计、
大概需要20分钟、
但是算起来比你这个还快很多呢、
在sqlmapclient.getMa*****忘了方法名了、自己找去吧、得到一个connection、
用完记得关闭
优化方法.
1.尽量将子查询语句变成连接语句.
2.连接语句是用来连接表与表之间的关系.发现有很多字符串存在连接公式中.请改用条件表达式
应该有明显效率改善
在百度知道里有个小例子你看看,希望对你有所帮助
http://zhidao.baidu.com/question/113049207.html
我说,这和java没有什么关系吧?你看看sql的执行时间和java的执行时间,是不是sql在20分钟内还没有执行完成啊?才6000条,再怎么也没你说的那么夸张啊
要不是就是表设计有问题,不一定要求完全三范式,要根据业务的需求而定啊这个优化SQL还不如看一下表的设是否合理
ON c.OWNERSHIP_OF_MNC = dic24.member_value
AND dic24.group_code = 'IX_COUNTRY'
AND dic24.sys_type = 'IDMS'
AND dic24.branch_code = '0912'
这些代码中的AND条件改成 LEFT JOIN一个子查询,先过滤表中的数据,再LEFT JOIN。这样就能省LEFT JOIN时间了。还有 SQL中尽量不要用 IN 关键字。 再者就是各个表的索引建得是否合理。 说实再的,这么大的SQL看着就让人寒心。执行效率肯定高不到哪去了。。 我只是抒发已见,有改进的地方,希望高手指点。不要喷我哦。。
语句凑。
可以将ci_sys_sov_member的数据在系统启动的时候存放到HashMap中,这个HashMap是个全局的,然后在java或jsp页面通过字典的键得到中文的值,这样任何地方涉及到需要将字典转换成中文的地方都可以用
1 索引
2 尽量用简单的SQLLZ可以试着把这个长SQL打散成短的简单的SQL,分几次运行得出最终结果,充分利用索引的优势。恐速度必然会提高。怕用JAVA循环6000次来找符合条件的记录都比这个SQL要快了。
我当然知道SQL长了,
但是客户就要将字典信息表中的内容显示出来。
直接查当然快,但是客户同意吗?
你不关联字典表,如何弄。一群人就知道瞎喊。
就这一个明白人。