这个语句可以优化一下性能么？

[lingyun410]

Select A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,Count(B.ID) As PoCount
From [CNode_Label] A
Right Join [CNode_Product] B
On CharIndex(','+Cast(A.ID As nVarChar(20))+',',B.PO_LabelLib)>0 And B.PO_StateNum In (5,6)
Where A.LB_StateNum In (5) And (CharIndex(',9,',A.LB_GroupIDStr)>0 And CharIndex(',1,',A.LB_GroupIDStr)>0)
Group By A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,A.LB_OrderNum Having Count(B.ID)>0
Order By PoCount Desc,A.LB_OrderNum Desc,A.ID Desc

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[dawugui]

没看出哪里还可以优化了,用chaindex等函数,索引失效了.

[lingyun410]

现在数据库的数据还不多，查询速度就很慢。查询的时候CPU占用很高。这个语句能优化么？

[htl258_Tony]

你的语句应该不错了。

[htl258_Tony]

出于这种需求，语句上看不出可以优化的地方

[永生天地]

Select A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,Count(B.ID) As PoCount  
From [CNode_Label] A  Right Join [CNode_Product] B  
On CharIndex(','+Cast(A.ID As nVarChar(20))+',',B.PO_LabelLib)>0 
where B.PO_StateNum In (5,6)  --这个条件写在on连接里是无效的
and A.LB_StateNum =5 
And (CharIndex(',9,',A.LB_GroupIDStr)>0 And CharIndex(',1,',A.LB_GroupIDStr)>0)  
Group By A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,A.LB_OrderNum 
Having Count(B.ID)>0  
Order By PoCount Desc,A.LB_OrderNum Desc,A.ID Desc

[笨沙发]

优化表结构，现在结构不合理