- C# code
select area.compname,cust.province,cust.cust_name,tb1.OrderNum,tb1.ItemNoNum,tb1.NoPick,count(1) as Overweight,Convert(decimal(5,2),(count(1)-(isnull(sum(case when esd.ex_no is null and bcs.service_no is null then 0 else 1 end), 0)))*100.00/count(1)) as UpPer, /*15*/ (select sum(case when (Promises IS not null and Promises <>'') and OrderDate between getdate()-15 and getdate() then 1 else 0 end) from FA_WMSDB.ServicePos.dbo.SnService where customer_id=tb1.customer_id) /*15*/ from #tb tb1 join BC_customer cust on tb1.customer_id=cust.customer_id join acercomp area on area.compcode=cust.compcode left join FA_WMSDB.ServicePos.dbo.SnService tb2 on tb1.customer_id=tb2.customer_id and (tb2.Promises<>'' and tb2.Promises is not null) left join [BC_serianos] bcs on bcs.serial_number=tb2.SerialNo left join [exclude_serialno_detail] esd on esd.serialno = tb2.SerialNo group by area.compname,cust.province,cust.cust_name,tb1.OrderNum,tb1.ItemNoNum,tb1.NoPick,tb1.customer_id
注:
FA_WMSDB.ServicePos.dbo.SnService 270万条数据.
#tb1 4000条数据 结构和FA_WMSDB.ServicePos.dbo.SnService 基本一致
问题:
查询速度慢.如果去掉 /*15*/以下的 就快了.如何优化
------解决方案--------------------
有两个/*15*/哦，你指的是那段？
------解决方案--------------------
试试
- SQL code
SELECT area.compname , cust.province , cust.cust_name , tb1.OrderNum , tb1.ItemNoNum , tb1.NoPick , COUNT(1) AS Overweight , CONVERT(DECIMAL(5 , 2) , (COUNT(1)-(ISNULL(SUM(CASE WHEN esd.ex_no IS NULL AND bcs.service_no IS NULL THEN 0 ELSE 1 END) , 0)))*100.00/COUNT(1)) AS UpPer , /*15*/ xx.oo /*15*/FROM #tb tb1JOIN BC_customer custON tb1.customer_id = cust.customer_idJOIN acercomp areaON area.compcode = cust.compcodeLEFT JOIN FA_WMSDB.ServicePos.dbo.SnService tb2ON tb1.customer_id = tb2.customer_id AND ( tb2.Promises <> '' AND tb2.Promises IS NOT NULL )LEFT JOIN [BC_serianos] bcsON bcs.serial_number = tb2.SerialNoLEFT JOIN [exclude_serialno_detail] esdON esd.serialno = tb2.SerialNoLEFT JOIN ( SELECT oo=SUM(CASE WHEN ( Promises IS NOT NULL AND Promises <> '' ) AND OrderDate BETWEEN GETDATE()-15 AND GETDATE() THEN 1 ELSE 0 END) FROM FA_WMSDB.ServicePos.dbo.SnService) xx ON xx.customer_id = tb1.customer_idGROUP BY area.compname , cust.province , cust.cust_name , tb1.OrderNum , tb1.ItemNoNum , tb1.NoPick , tb1.customer_id