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

[cnyu]

有2个表:

Orders 表
字段: OrderNo, Active, RemainVolume, OrderDesc, BinaryData

OrderNo: bigint
Active: bool
RemainVolume: int
OrderDesc: varchar
BinaryData: image(binary)

ChildOrders 表
字段: OrderNo, ParentOrderNo

OrderNo: bigint
ParentOrderNo: bigint

OrderNo 是这个2个表的关键字段


现在要选择表Orders中的所有数据,条件:
1. Active = True AND
(
2. RemainVolume > 0 OR
3. RemainVolume = 0 但是Child的RemainVolume > 0
)
结果要显示Orders表中的所有字段,即OrderNo, Active, RemainVoluem, OrderDesc, BinaryData
而且OrderNo是唯一的,没有重复记录

注意: 因为Orders表中含有BinaryData,所以,不能用distinct
因为记录非常多, 上亿条记录,所以,要求有很好的性能

大家帮忙想想, 有啥好办法. 先行谢过了

[cnyu]

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

[魔者侠心之明星级程序员]

试一下： select * from Orders where RemainVolume > 0 and Active = true UNION select 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 )

[seusoftware]

引用 6 楼 cnyu 的回复:

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

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

[cnyu]

Thanks. But what are CO1, OD2?

[cnyu]

引用 10 楼 seusoftware 的回复:

引用 6 楼 cnyu 的回复:谢谢 不过,请注意ChildOrders是没有RemainVolume这个字段的 看你这样写的： 但是Child的RemainVolume > 0

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

[魔者侠心之明星级程序员]

引用 11 楼 cnyu 的回复:

Thanks. But what are CO1, OD2?

引用 11 楼 cnyu 的回复:

Thanks. But what are CO1, OD2?

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

[cnyu]

引用 13 楼 SQL_Beginner 的回复:

引用 11 楼 cnyu 的回复:Thanks. But what are CO1, OD2? 引用 11 楼 cnyu 的回复:Thanks. But what are CO1, OD2? 分别为2 个 Orders 的别名，不然最后一句and CO2.RemainVolume > 0 and Active = true 你怎么知道是那个Orde……

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

[cnyu]

引用 9 楼 SQL_Beginner 的回复:

试一下： select * from Orders where RemainVolume > 0 and Active = true UNION select o.* from Orders o where o.OrderNo in ( SELECT CO2.OrderNo FROM Orders OD1 inner join Ch……

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

[魔者侠心之明星级程序员]

引用 14 楼 cnyu 的回复:

引用 13 楼 SQL_Beginner 的回复: 引用 11 楼 cnyu 的回复:Thanks. But what are CO1, OD2? 引用 11 楼 cnyu 的回复:Thanks. But what are CO1, OD2? 分别为2 个 Orders 的别名，不然最后一句and CO2.RemainVolume > 0 and Active = tru……

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

[cnyu]

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

[cnyu]

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

[魔者侠心之明星级程序员]

引用 18 楼 cnyu 的回复:

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

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

[魔者侠心之明星级程序员]

请把完整的T-SQL语句发出来

[cnyu]

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

[cnyu]

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

[cnyu]

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

[seusoftware]

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这个列应该是剩余量吧，加个索引帮助检索；

[cnyu]

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

[cnyu]

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