加上一个条件，查询速度变慢，大家帮我分析下什么原因？

[hbqc_zh]

select a.cCusCode,a.cSOCode,a.dDate,a.cMaker,a.cVerifier,a.dverifysystime,a.cMemo,b.AutoID,b.cInvCode,b.iQuantity,b.iNum,
b.dPreDate,b.cMemo as bcMemo,
c.cInvName,c.cInvStd,c.cInvCCode,c.fMinSupply,c.cInvDefine1,c.cInvDefine9,c.cInvDefine4,
c.cInvDefine5,c.cInvDefine6,c.cDefWareHouse,c.cInvDepCode,
d.cComUnitName,e.cWhName,f.cDepName,h.iMoQuantity,h.iMoNum,
i.sort,i.OrderID,q.XDiQuantity,w.PackageLength from SO_SOMain a
inner join SO_SODetails b on a.id=b.id
inner join Inventory c with(nolock) on b.cInvCode=c.cInvCode
left join ComputationUnit d on c.cComUnitCode=d.cComunitCode
left join Warehouse e on c.cDefWareHouse =e.cWhCode left join Department f on c.cInvDepCode=f.cDepCode
left join Customer g on a.cCusCode=g.cCusCode
left join (select a.cInvCode as w_cInvCode,b.PackageLength from XyZZ_BaseDesignCard a
inner join XyZZ_BaseCertificate b on a.DesignCard_ID=b.DesignCard_ID Where a.IsDefaultVer = 1) as w on b.cInvCode=w.w_cInvCode
left join (select AutoID,sum(isnull(iMoQuantity,0))as iMoQuantity,sum(isnull(iMoNum,0)) as iMoNum from XyZZ_SCMoOrderS
group by AutoID) as h on b.AutoID=h.AutoID left join XyZZ_ScMoComplete i on b.Autoid=i.OrderID

left join (
select a.cInvCode,isnull(j.Or_iQuantity,0)+isnull(a.iSafeNum,0)-isnull(h.KiQuantity,0)-isnull(k.Sc_iQuantity,0)
+isnull(L.DBiQuantity,0) as XDiQuantity
from Inventory a with(nolock)
left join (select a.cInvCode,sum(iQuantity) as SQuantity,sum(iQuantity+fInQuantity-fOutQuantity) as KiQuantity
from CurrentStock a inner join Warehouse b on a.cWhCode=b.cWhCode where b.bMRP=1 group by a.cInvCode) as h on a.cInvCode=h.cInvCode
left join (SELECT a.cInvCode,SUM(ISNULL(a.iQuantity, 0) - ISNULL(a.iFHQuantity, 0)) AS Or_iQuantity,
SUM(IsNull(a.iNum, 0) - IsNull(a.iFHNum, 0)) As Or_iNum FROM SO_SODetails a
INNER JOIN SO_SOMain b ON a.ID = b.ID WHERE (ISNULL(a.cSCloser, '') = '') and (ISNULL(b.cVerifier, '') <> '')
AND (ISNULL(a.iQuantity, 0) >= ISNULL(a.iFHQuantity, 0)) AND (ISNULL(a.iNum, 0) >= ISNULL(a.iFHNum, 0))
GROUP BY a.cInvCode) as j on a.cInvCode=j.cInvCode left join (select a.cInvCode,sum(isnull(a.iQuantity,0)-isnull(h.IniQuantity,0)) as Sc_iQuantity from XyZZ_SCMoOrder a left join (select a.cDefine34,a.cInvCode,sum(isnull(a.iQuantity,0)) as IniQuantity, sum(isnull(a.iNum,0)) as IniNum from rdrecords10 a inner join rdrecord10 b on a.id=b.id where a.cDefine34 is not null and a.cDefine35='8' group by a.cDefine34,a.cInvCode) h on a.SCMoOrder_ID=h.cDefine34 and a.cInvCode=h.cInvCode where a.RUser is not null and (a.JUser is null or JUser='') and isnull(a.iQuantity,0)-isnull(h.IniQuantity,0)>0 group by a.cInvCode) as k on a.cInvCode=k.cInvCode left join (SELECT a.cInvCode,sum(isnull(a.iTVChkNum,0)-isnull(a.iTVSumNum,0)) as DBiNum, sum(IsNull(a.iTvChkQuantity, 0) - IsNull(a.iTvSumQuantity, 0)) As DBiQuantity FROM ST_AppTransVouchs a INNER JOIN ST_AppTransVouch b ON a.ID = b.ID
WHERE (isnull(a.cBCloser,'')='') and (isnull(b.cVerifyPerson,'')<>'') and
isnull(a.iTVChkNum,0)>=isnull(a.iTVSumNum,0) and isnull(a.iTvChkQuantity,0)>=isnull(a.iTvSumQuantity,0)
GROUP BY a.cInvCode) as L on a.cInvCode=L.cInvCode) as q on b.cInvcode=q.cInvcode

where a.cVerifier is not null and b.cSCloser is null and i.OrderID is null and
dverifysystime >='2014-08-06 0:00:00' and dverifysystime<='2016-08-13 13:00:00'

--加此条件查询速度慢，是什么原因？
and c.cInvCCode like '9101001'

and isnull(b.iQuantity,0)-isnull(h.iMoQuantity,0)>0


order by a.dDate asc

[hbqc_zh]

ding 谢谢了

[中国风]

cInvCCode 有没有过索引或造成表或索引扫描 c.cInvCCode= '9101001' 用“=”再试试

[hbqc_zh]

引用 2 楼 roy_88 的回复:

cInvCCode 有没有过索引或造成表或索引扫描 c.cInvCCode= '9101001' 用“=”再试试

没有索引，很奇怪的问题，本机速度也挺快，在服务器上就慢了

[专注or全面]

有可能是服务器上数据跟你本地数据不一样，比如原始条件是查询结果是100行，服务器上可能是2000行 另外你的问题也是一个比较经典的问题，很多人都有这样的疑问，就是说我又很详细的查询条件，然后目标结果集并不大，为啥会比原来（查询条件少的时候满），完全有这种可能，查询条件越多，数据库要做的判断就越多，要耗费的时间就越多。尤其是在数据量比较大，查询比较复杂的情况下。 你这个问题可以通过合适的复合索引来解决，看看把你新加的查询条件列加在某一个索引上，试试看 比如这样 create index idx_name on tableName（原来的索引列，新加查询条件的索引列）

[中国风]

你看一下计划，是否在服务器没有并行计划，本机有并行计划 在列建上过索引

[kingtiy]

如果不加这个条件查询挺快的话,你可以先不加. 做子查询的时候,再对前面的结果做一次查询,把这个条件加上去,是不是就可以了

[hbqc_zh]

引用 5 楼 kingtiy 的回复:

如果不加这个条件查询挺快的话,你可以先不加. 做子查询的时候,再对前面的结果做一次查询,把这个条件加上去,是不是就可以了

试过了，不行

[阿拉敏敏]

先在你的C表的里面做一次筛选加上判断条件试下速度

[hbqc_zh]

引用 8 楼 hzfqq333 的回复:

先在你的C表的里面做一次筛选加上判断条件试下速度

感谢各位，已使用临时表解决了此问题，平分分数。