dao.execute 查询耗时问题

查询条件索引都加了 Navicat工具查询速度不到1秒但是写到程序里就得用6 7秒怎么优化

  select userName  as  gsmc,invoice as  sh,c.kpjh as  kpjh,companyName as  cjd,COALESCE(f.SL,0) as SL,COALESCE(f.pp,0) as ppNum,COALESCE(f.zp,0) as zpNum,COALESCE(f.jdcp,0) as jdcNum,COALESCE(f.zje,0) as totalJE,COALESCE(f.zse,0) as totalSE  from tb_userinfo c left join (select xfsh,kpjh,count(fphm) as SL, count(if((fpzl='c' or fpzl='t'),true,null)) AS pp,count(if(fpzl='s',true,null)) AS zp,count(if(fpzl='j',true,null)) AS jdcp,COALESCE(SUM(JSHJ),0) as  zje,COALESCE(SUM(HJSE),0) as zse from tb_xxfp where 1=1  and zfbz=0  and ssyf = '201712'GROUP BY xfsh, kpjh ) as f  on c.invoice = f.XFSH and c.kpjh = f.KPJH where 1=1    order by invoice ,c.kpjh

wendal

1楼•2541天前

数据量多少, navicat是否把全部数据都显示了?

mrwangzh

2楼•2541天前

3百万左右

mrwangzh

3楼•2541天前

这样写有问题没？

	ss = Sqls.queryRecord(sql); 
			ss.setCallback(Sqls.callback.entities());
			Entity<XXFP> e = dao.getEntity(XXFP.class);
			ss.setEntity(e);
			dao.execute(ss);
			List<XXFP> listdy = ss.getList(XXFP.class);

wendal

4楼•2541天前

结果集有多少数据?

mrwangzh

5楼•2541天前

800多条

wendal

6楼•2541天前

去掉setCallback, getList会返回null, 只为了看耗时.

mrwangzh

7楼•2541天前

额。。摘掉以后只要9毫秒那我要怎么改是直接返回一个map吗