如何解决这个问题而且有好的sql performance

Question

本帖最后由 cnyu 于 2013-04-09 12:16:18 编辑

Answer 1

想法是:
1. 选出所有的Active=1 以及 RemainVolume > 0 的记录
2. 找出1中结果集中所有Order的ParentOrder
3. 将结果1和2合并,并且去掉重复记录问题是: 这里要用到join操作,而且因为Orders表不能进行select distinct的操作, 而且还要考虑性能, 就很难写了

Answer 2

我现在没有找到好的解决办法, 实际问题的表比这2个复杂, 我这里只列出2个表的关键字段和要求
我目前还没有找到比较好的stored procedure去解决这个问题

Answer 3

example data:Orders:
OrderNo Active RemainVolume OrderDesc BinaryData
1 1 0 "test1" <BLOB>
2 1 0 "test2" <BLOB>
3 0 100 "test3" <BLOB>
4 1 200 "test4" <BLOB>
5 1 300 "test5" <BLOB>
6 1 400 "test6" <BLOB>ChildOrders:
OrderNo ParentOrderNo
1 1
2 2
3 1
4 2
5 2
6 6
Result should be:
OrderNo Active RemainVolume OrderDesc BinaryData
4 1 200 "test4" <BLOB>
5 1 300 "test5" <BLOB>
6 1 400 "test6" <BLOB>
2 1 0 "test2" <BLOB>OrderNo 为4,5,6的被选出来,很显然是因为Active=1而且RemainVolume>0
OrderNo 为2被选择出来,是因为它的子Order 4 和 5 的RemainVolume > 0

Answer 4

select o.* 
from Orders o 
inner join ChildOrders co
on o.OrderNo = co.OrderNo
where o.Active = 1 AND
(
 o.RemainVolume > 0  OR (o.RemainVolume = 0 and co.RemainVolume > 0)
)OrderNo在2个表上要有索引，不过唯一的话，建议加主键或者唯一键(相信已经有了:p)，这样索引效率高些；
Active这个列索引没什么效果，应为是bool型；
ReainVolum这个列应该是剩余量吧，加个索引帮助检索；

Answer 5

谢谢
不过,请注意ChildOrders是没有RemainVolume这个字段的

Answer 6

这个问题的头疼指处就在于ChildOrders这个表只有OrderNo的关系, 没有其它任何字段, 而结果集却要求Order标中的所有字段, 我觉得可能会类似于:
select o.* from Orders
where o.OrderNo in
(
    ......
)

Answer 7

OrderNo 在2个表中都是主键而且唯一

Answer 8

试一下：
select * from Orders
where RemainVolume > 0  and  Active = true UNIONselect o.* from Orders o
where  o.OrderNo in
(
    SELECT CO2.OrderNo  FROM Orders OD1 inner join ChildOrders CO 
    on OD1.OrderNo =CO.ParentOrderNo and (CO1.RemainVolume = 0  or CO1.Active = false)      
                                 inner join Orders CO2 on OD2.OrderNo =CO.ParentOrderNo
       and CO2.RemainVolume > 0  and  Active = true 
)

Answer 9

看你这样写的：
但是Child的RemainVolume > 0

Answer 10

Thanks.But what are CO1, OD2?

Answer 11

不好意思,误导你了, 我写的是描述性质的, Child 和 Parent 是通过表ChildOrders展现出来的
实际上所有的结果记录都来自Orders表,只是条件比较复杂,要用到ChildOrders表找出Orders表中的记录中存在的一些关系

Answer 12

分别为2 个 Orders 的别名，不然最后一句and CO2.RemainVolume > 0  and  Active = true 你怎么知道是那个Orders 的列呢？ 最后一句应该改为 CO2.RemainVolume > 0  and  CO2.Active = true ，不然有语法错误。

Answer 13

谢谢. 但是两个表的记录数太多, 两个inner join 加上union操作, Performance恐怕难以达到要求

Answer 14

另外,因为Orders表中含有image data, 所以不能用union操作:The image data type cannot be selected as DISTINCT because it is not comparable

Answer 15

我给你提供2个思路，你自己想一下，
1，根据查询计划，进行优化。
2，INDEXED VIEW ，这个最快了，当然也会有额外的开销。

Answer 16

其实我告诉你的Orders这个表已经是个view了

Answer 17

本问题中的Orders表可以视为一个indexed view

Answer 18

那重新根据你的需求再定义一个indexed view