SQL server2008数据库，为啥在management studio里面执行速度大于nutzdao的速度

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

打印的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查询慢很多，不知道什么原因

wendal

2楼•1469天前

参与group by的数据很多？

看上去不需要分页吧，去掉分页试试？

qq_2ac473ac 3楼•1469天前

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

请先登陆