第一句:
in和or太多,用exists代替(部分你已经用exists了,应该会改的)
第二句:
检查索引情况,特别是几个连接字段和排序字段
第三句:
嵌套查询这么多,索引都用不上了
(FNumber=ir.FNumber1 OR(FNumber<>ir.FNumber1 and FNumber Like ir.FNumber1+'.%' ))
应该可以改成
(FNumber>=ir.FNumber1 and FNumber<cast(ir.FNumber1 as int)+1) 最后改成:
Select distinct t_Item.*
From t_Item ,(
Select i.FItemID FItemID1 ,i.FNumber FNumber1
From t_ItemRight r,t_Item i
Where FTypeID=1
AND r.FItemID=i.FItemID
AND (FUserID =22 OR FUserID=16708)
) ir
Where (t_Item.FNumber>=ir.FNumber1 and t_Item.FNumber<cast(ir.FNumber1 as int)+1)
AND t_Item.FDeleteD=0 And t_Item.FItemClassID = 1
And (t_Item.FItemID = 255)
Order by t_Item.FItemClassID, t_Item.FNumber
--没有测试
in和or太多,用exists代替(部分你已经用exists了,应该会改的)
第二句:
检查索引情况,特别是几个连接字段和排序字段
第三句:
嵌套查询这么多,索引都用不上了
(FNumber=ir.FNumber1 OR(FNumber<>ir.FNumber1 and FNumber Like ir.FNumber1+'.%' ))
应该可以改成
(FNumber>=ir.FNumber1 and FNumber<cast(ir.FNumber1 as int)+1) 最后改成:
Select distinct t_Item.*
From t_Item ,(
Select i.FItemID FItemID1 ,i.FNumber FNumber1
From t_ItemRight r,t_Item i
Where FTypeID=1
AND r.FItemID=i.FItemID
AND (FUserID =22 OR FUserID=16708)
) ir
Where (t_Item.FNumber>=ir.FNumber1 and t_Item.FNumber<cast(ir.FNumber1 as int)+1)
AND t_Item.FDeleteD=0 And t_Item.FItemClassID = 1
And (t_Item.FItemID = 255)
Order by t_Item.FItemClassID, t_Item.FNumber
--没有测试
SELECT * FROM t_Account,(Select Distinct c1.FAccountID FItemID From t_Account c1,(Select Distinct r.FItemID FItemID,FNumber FNumber1 From t_ItemRight r,t_Account a Where (r.FAuthtype & 2=2) AND FTypeID=105 AND r.FItemID=a.FAccountID AND (FUserID IN (Select FGroupID From t_Group Where FGroupID =21 ) OR FUserID=16411)) c Where c1.FNumber=c.FNumber1 OR PATINDEX(c.FNumber1+'.%',c1.FNumber)>0 ) c WHERE ( FDelete=1 Or FDelete=0 Or FIsAcnt=1) AND FAccountID=c.FItemID ORDER BY FNumber