sql语句查询条件的值改变速度无比慢?
select
distinct b.HoursProductionID, b.WorkBill,b.ContractNO,b.ProductName,b.ContractQty,b.Unit, b.BindSubType,b.bussinessID,
g.MasterType,g.SubType,h.DepartmentCode,h.TradeType,g.Qty,
ISNULL(case when b.IsDuplicate=1 and len(b.WorkBillFlag)=9 then 0
else ( select convert(decimal(18,4),x.CostPrice) from GetRationCostByWorkBill (b.WorkBillFlag,b.bussinessID) x ) end,0) as RationCost,--定量材料价格
ISNULL(SUM(CASE WHEN charindex('印刷',d.ProcessName) > 0 THEN (a.UnitProduction * c.FixMaterial) ELSE (a.Production * c.FixMaterial) END ),0)AS QuotaCost, --定额材料价格
ISNULL(SUM(CASE WHEN charindex('印刷',d.ProcessName) > 0 THEN (a.UnitProduction * c.LimitMaterial) ELSE (a.Production * c.LimitMaterial) END),0) AS LimitCost, --限量材料
ISNULL(SUM(CASE WHEN charindex('印刷',d.ProcessName) > 0 THEN (a.UnitProduction * c.PointMaterial) ELSE (a.Production * c.PointMaterial) END),0) AS PointCost,--定点材料
ISNULL(SUM(a.Hours * c.FixArtificial),0) AS ArtifCost,--人工
ISNULL(SUM(a.Hours * c.Utility),0) AS UtilityCost, --水电
ISNULL(SUM(a.Hours * c.ChangeCost),0) AS ChangeCost,--制造费用
ISNULL(SUM(a.Hours * c.FactoryRent),0) AS FactoryCost, --厂租
ISNULL(SUM(a.Hours * c.Depreciation),0) AS DepreCost, --折旧
ISNULL(i.Amount,0 ) as ZZDeptCost, --装帧成本
ISNULL(e.NoTaxAmount,0 ) as TMCost, --烫金、激光模切:生产采购费用
ISNULL(f.NoTaxAmount,0 ) as OutCost, --外发加工费
ISNULL( case when b.IsDuplicate=1 then 0
else g.Amount end, 0) as SalesRevenue --销售收入
from Cost_HoursProductionDetail a
left join Cost_HoursProduction b on a.HoursProductionID=b.HoursProductionID
left join Cost_ProcessFixData c on a.ProcessFixDataID=c.ProcessFixDataID
left join Cost_Process d on a.ProcessID=d.ProcessID
left join (
SELECT sum(NoTaxAmount) as NoTaxAmount,WorkBill,bussinessID
FROM dbo.Cost_ProductionProcureDetail AS x1 INNER JOIN dbo.Cost_ProductionProcurement AS x2 ON x1.ProcureID = x2.ProcureID
WHERE (x2.ApproveStatus = 9) group by x1.WorkBill,x2.bussinessID
) e on e.WorkBill=b.WorkBill and e.bussinessID=b.bussinessID
left join (
SELECT sum(NoTaxAmount) as NoTaxAmount,WorkBill,bussinessID
FROM dbo.Cost_OutSideDetail AS x1 INNER JOIN dbo.Cost_OutSide AS x2 ON x1.OutSideID = x2.OutSideID
WHERE (x1.IsSpecial = '是') AND (x2.ApproveStatus = 9) group by x1.WorkBill,x2.bussinessID
) f on f.WorkBill=b.WorkBill and f.bussinessID=b.bussinessID
left join
(
SELECT sum(Amount) as Amount,WorkBill,bussinessID
FROM dbo.Cost_BookBindDetail AS x1 INNER JOIN dbo.Cost_BookBinding AS x2 ON x1.BookBindID = x2.BookBindID
WHERE (x2.ApproveStatus = 9) group by x1.WorkBill,x2.bussinessID
) i on i.WorkBill=b.WorkBill and i.bussinessID=b.bussinessID
left join Cost_ContractDetail g on g.ContractNO=b.ContractNO and g.ProductName=b.ProductName
left join Cost_Contract h on h.ContractID=g.ContractID
where (g.Amount>0 or h.IsNoAmountContract='是')
and b.ApproveStatus=9
and h.ApproveStatus=9
and (
( b.ApproveEndDt>='2013-11-01'
and convert(nvarchar,b.ApproveEndDt,23)<='2013-11-30'
and h.ApproveEndDt>='2013-11-01' and convert (nvarchar,h.ApproveEndDt,23)<='2013-11-30'
)
or( h.ApproveEndDt<='2013-11-01'
and b.ApproveEndDt>='2013-11-01' and convert(nvarchar, b.ApproveEndDt,23)<='2013-11-30'
)
or( b.ApproveEndDt<='2013-11-01'
and h.ApproveEndDt>='2013-11-01' and convert(nvarchar, h.ApproveEndDt,23)<='2013-11-30'
)
)
and B.BussinessID='132'//如果去掉这个条件,或者BussinessID=其他值,速度较 快,大概6,7秒出数据。
group by b.HoursProductionID,b.ContractNO,b.ProductName,
b.WorkBill,b.workbillFlag,b.ContractQty,b.bussinessID,b.Unit,
b.ApproveEndDt,b.IsDuplicate,
g.MasterType,g.SubType,b.BindSubType,
h.DepartmentCode,h.TradeType,g.Qty,
g.Amount,e.NoTaxAmount,f.NoTaxAmount,i.Amount
说明:B.BussinessID='132'//速度巨慢,180秒,如果去掉这个条件,或者BussinessID=其他值,速度较 快,大概6,7秒出数据。测试发现如果注释掉ISNULL(SUM(a.Hours * c.FactoryRent),0) AS FactoryCost, --厂租
这些带有ISNULL的语句整个查询变得特别快。
请高手帮忙指点一下,实在没辙了,谢谢! sql 查询 慢
[解决办法]
这个需要看执行计划,才知道是哪个地方出的问题
[解决办法]
这个BussinessID 字段是什么类型的,一般看ID都是int类型的把。
你改成这样试试,能不能快点:
B.BussinessID= 132
[解决办法]
1、统计信息不够新,update statistics '用到的表'
2、数据分布不均匀,查询过程中应该用了并行执行(可以看执行计划),查查sselect top 5 * from sys.dm_os_wait_stats order by wait_time_ms DESC
应该会有CXPACKET这些等待状态
3、贴出执行计划
[解决办法]
这么一大堆,不慢才怪呢