标题: 大家给点优化意见 [打印本页] 作者: 第八颗北斗星 时间: 2011-1-24 17:50 标题: 大家给点优化意见 下面是页面一个点击查询之后，后台执行的语句，我觉得单从sql优化已经不现实了，大家给我点建议
下面只是截取的一部分，共有45个sql：
public List getData(Date beginDate, Date endDate, String brch_id) {
// -------------------买入业务start---------------------------
// 直贴
sql[0] ="select '直贴',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=1 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=1 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=1 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=1 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 票据包买
sql[1] ="select '票据包买',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=2 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=2 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=2 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=2 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 商票保贴
sql[2] ="select '商票保贴',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=3 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=3 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=3 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=3 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 代理贴现
sql[3] ="select '代理贴现',sum(table.num),sum(coalesce(table.all_money,0)) from "
+"((select count(*) as num,sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=4 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ") union all select count(*) as num,sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=4 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (1,2) and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all "
+"(select count(*) as num,-sum(bnfo_bill_money) all_money "
+ "from buybatch ,re_buba_bnfo "
+ "where buybatch.buba_id=re_buba_bnfo.buba_id and buybatch.bupr_id=4 and rebb_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")) union all select count(*) as num,-sum(bk_bill_money) all_money "
+ "from Back_deleted_bill "
+ "where upper(table_name)='RE_BUBA_BNFO' and bk_prod_type=4 and re_bill_id "
+ "in (select re_bill_id "
+ "from busiflow where busitype='BUY' and bufi_status in (2) and bufl_no in(select pre_bufl_no from busiflow where bufi_code='CZ' and bufi_status=4 and bufl_dt='"
+ beginDate + "' and brch_id='" + brch_id + "'"
+ ")))) table";
// 转贴现买断作者: msnshow 时间: 2011-1-24 19:55
那就从业务上来优化作者: PrefTest 时间: 2011-1-24 23:08
什么数据库？一般需要从索引、表结构、SQL语句几个方面入手优化作者: 云层 时间: 2011-1-25 12:36
看到查询嵌套那么问题就来了，可以考虑中间过程用视图来解决或者你要考虑索引和设计了