NutzCN Logo
问答 SQL server2008数据库,为啥在management studio里面执行速度大于nutzdao的速度
发布于 1202天前 作者 qq_2ac473ac 1079 次浏览 复制 上一个帖子 下一个帖子
标签:

除了缓存原因,还有没有其他原因会造成这样?因为无论怎么更改查询范围,management studio的速度都远远大于nutzdao的速度

3 回复

打印的sql

select * from(select row_number()over(order by __tc__)__rn__,* from(select top 100 0 __tc__,  rtrim(branch.branch_no) branchid,branch.branch_name branchName,saleflow.item_subno Barcode,goods.item_subname subName ,SUM(saleflow.sale_money)/sum(sale_qty) avgSalePrice, sum(sale_qty) saleQty,SUM(saleflow.sale_money) saleMoney, SUM(saleflow.in_price*saleflow.sale_qty)/sum(sale_qty) avgInPrice,SUM(saleflow.sale_qty*(saleflow.sale_price-saleflow.in_price)) saleProfit,SUM(saleflow.sale_qty*(saleflow.sale_price-saleflow.in_price))/SUM(saleflow.sale_qty) avgProfit,SUM(saleflow.sale_qty*(saleflow.sale_price-saleflow.in_price))/SUM(saleflow.sale_money) profitRate,stock.stock_qty stockQty,stock.cost_amt stockAmt from pos_t_saleflow saleflow left join  pos_t_salemaster salemaster on saleflow.flow_no =salemaster.flow_no left join bi_t_branch_info branch on branch.branch_id = salemaster.branch_id left join fqx_v_goodsall goods on goods.item_id = saleflow.item_id left join bi_t_supcust_info supcust on supcust.supcust_id=goods.sup_id left join bi_t_item_cls cls on cls.item_clsid=goods.item_clsid left join ic_t_branch_stock stock on stock.branch_id=branch.branch_id and goods.item_id=stock.item_id   WHERE salemaster.trade_date BETWEEN '20200125' AND '20200126' GROUP BY branch.branch_no,branch.branch_name,saleflow.item_subno,goods.item_subname ,goods.item_id,stock.stock_qty,stock.cost_amt having SUM(saleflow.sale_money)>0  ORDER BY saleQty DESC  )t)tt where __rn__ > 0

其中trade_date 是聚集索引

Sql sql = Sqls.create(conf.get("beforedaysumlist")); 
		sql.setVar("cris", cri);
		sql.setPager(pager);
		sql.setCallback(Sqls.callback.entities());
	    Entity<GoodsAnalysis> entity = dao.getEntity(GoodsAnalysis.class);
		sql.setEntity(entity);
		long st=System.currentTimeMillis();
		dao.execute(sql);
		System.out.println("shuju用时================="+(System.currentTimeMillis()-st)/1000);
		List<GoodsAnalysis> goodsaAnalysis=sql.getList(GoodsAnalysis.class);

dao查询慢很多,不知道什么原因

参与group by的数据很多?

看上去不需要分页吧,去掉分页试试?

6,7个group by。试了一下,好像不是分页的问题。去掉了也很慢。刚刚测试了一下trade_date 是char类型的,trade_date =‘20200101’,和trade_date BETWEEN '20200101' AND '20200101'为啥效率差了很多啊?

添加回复
请先登陆
回到顶部