查询条件的一点不同,导致查询执行根本不同的奇怪问题。 用SET SHOWPLAN_TEXT查看执行的详细信息 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 设置3.15的查询执行结果:(9行) |--Sort(ORDER BY:([Expr1003] DESC)) |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1009]))) |--Stream Aggregate(GROUP BY:([b].[ITEM_ID], [a].[STORE]) DEFINE:([Expr1009]=Count(*), [a].[ITEM_ID]=ANY([a].[ITEM_ID]))) |--Sort(DISTINCT ORDER BY:([b].[ITEM_ID] ASC, [a].[STORE] ASC, [a].[FLOWNO] ASC)) |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[ITEM_ID], [c].[FLOWNO], [c].[STORE])) |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[FLOWNO], [c].[STORE])) | |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY].[PK__BUY__4282C7A2] AS [c]), WHERE:(([c].[AMOUNT]>0.00 AND [c].[SALEDATE]>'03 15 2008 12:00AM') AND [c].[SALEDATE]<='03 26 2008 12:00AM')) | |--Clustered Index Seek(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [a]), SEEK:([a].[STORE]=[c].[STORE]), WHERE:(([a].[ITEM_ID]=1 AND [a].[PRICE]<>0.00) AND [a].[FLOWNO]=[c].[FLOWNO]) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [b]), SEEK:([b].[STORE]=[c].[STORE]), WHERE:(([a].[ITEM_ID]<>[b].[ITEM_ID] AND [b].[PRICE]<>0.00) AND [b].[FLOWNO]=[c].[FLOWNO]) ORDERED FORWARD) 设置SET SHOWPLAN_TEXT on后2.15的查询执行结果 |--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC)) |--Sort(ORDER BY:([Expr1003] DESC)) |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1011]))) |--Hash Match(Aggregate, HASH:([a].[STORE], [b].[ITEM_ID]), RESIDUAL:([a].[STORE]=[a].[STORE] AND [b].[ITEM_ID]=[b].[ITEM_ID]) DEFINE:([Expr1011]=COUNT(*), [a].[ITEM_ID]=ANY([a].[ITEM_ID]))) |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [b].[ITEM_ID])) |--Hash Match(Aggregate, HASH:([a].[STORE], [b].[ITEM_ID], [a].[FLOWNO]), RESIDUAL:(([a].[STORE]=[a].[STORE] AND [b].[ITEM_ID]=[b].[ITEM_ID]) AND [a].[FLOWNO]=[a].[FLOWNO]) DEFINE:([a].[ITEM_ID]=ANY([a].[ITEM_ID]))) |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [b].[ITEM_ID], [a].[FLOWNO])) |--Hash Match(Inner Join, HASH:([c].[STORE], [c].[FLOWNO])=([b].[STORE], [b].[FLOWNO]), RESIDUAL:(([b].[STORE]=[c].[STORE] AND [b].[FLOWNO]=[c].[FLOWNO]) AND [a].[ITEM_ID]<>[b].[ITEM_ID])) |--Bitmap(HASH:([c].[STORE], [c].[FLOWNO]), DEFINE:([Bitmap1010])) | |--Hash Match(Inner Join, HASH:([c].[STORE], [c].[FLOWNO])=([a].[STORE], [a].[FLOWNO]), RESIDUAL:([a].[STORE]=[c].[STORE] AND [a].[FLOWNO]=[c].[FLOWNO])) | |--Bitmap(HASH:([c].[STORE], [c].[FLOWNO]), DEFINE:([Bitmap1009])) | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([c].[STORE], [c].[FLOWNO])) | | |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY].[PK__BUY__4282C7A2] AS [c]), WHERE:(([c].[AMOUNT]>0.00 AND [c].[SALEDATE]>'02 15 2008 12:00AM') AND [c].[SALEDATE]<='03 26 2008 12:00AM')) | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [a].[FLOWNO]), WHERE:(PROBE([Bitmap1009])=TRUE)) | |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [a]), WHERE:([a].[ITEM_ID]=1 AND [a].[PRICE]<>0.00)) |--Parallelism(Repartition Streams, PARTITION COLUMNS:([b].[STORE], [b].[FLOWNO]), WHERE:(PROBE([Bitmap1010])=TRUE)) |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [b]), WHERE:([b].[PRICE]<>0.00)) 不知道dawugui有什么好的测试建议。我是想要知道为什么会出现这样的情况。是特定的数据分布、还是别的什么原因。另外,现在saledate是允许为空的。如果设置为不为空,则这个情况消失。 --我也遇到过这情况,用between也特慢,最后改成这样datediff(dd,字段,'2008-01-01')>=0and datediff(dd,字段,'2008-01-10')<=0 --就可以了,看执行计划也没看出原因 你看看这两帖.http://topic.csdn.net/u/20071210/10/b7cfec7a-080b-4a30-b62a-92c987d3bcdd.htmlhttp://topic.csdn.net/u/20080319/09/e368fae5-cb6b-4cf7-bdc8-49351500c056.html?seed=2060429478 看来是跟查询优化器自动选择了适合数据的执行方式有关。不过Nested Loops应该是对排序过的数据做匹配,Hash Match应该是对先生成哈希表再匹配,为什么会用到这样两种方式呢? 问一下:1、buy表STORE, FLOWNO两字段的重复率,或者简单点,贴出select count(distinct STORE),count(distinct FLOWNO) from buy的结果2、buy表数据量3、试一下加索引 buy(saledate,amount),看有没有效果 如果日期是从3.15开始,则需要6秒因为3.15要打假!花去5秒看这么那么多price<>0就不爽,改>0可以不?另外把时间限定放在where的最前面try try flairsky: 多谢提醒。Haiwer:谢谢。问题很好解决,我是想知道两种执行方式的不同。zjcxc: 谢谢。能否解释一下什么时候会用Nested Loops,什么时候会用Hash Match? sql server 2005如何删除字段? 数据库被删除,有没有工具恢复呀!!! sql字符串问题 Paradox数据库插入数据问题,大侠们来看看啊! 请各位数据库高手帮我解答数据库问题 关于循环求和的语句 ASP在MSSQL数据库中查询不了 sql server 2005 中workstation component 安装失败! 行列转换碰到一条记录有字符出错的问题。帮我看看代码那里有问题。 Oracle JDBC 连接的问题(200分) 一个数据库中联合查询的问题 sqlserver导出数据到excel2003
3.15的查询执行结果:(9行)
|--Sort(ORDER BY:([Expr1003] DESC))
|--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1009])))
|--Stream Aggregate(GROUP BY:([b].[ITEM_ID], [a].[STORE]) DEFINE:([Expr1009]=Count(*), [a].[ITEM_ID]=ANY([a].[ITEM_ID])))
|--Sort(DISTINCT ORDER BY:([b].[ITEM_ID] ASC, [a].[STORE] ASC, [a].[FLOWNO] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[ITEM_ID], [c].[FLOWNO], [c].[STORE]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([c].[FLOWNO], [c].[STORE]))
| |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY].[PK__BUY__4282C7A2] AS [c]), WHERE:(([c].[AMOUNT]>0.00 AND [c].[SALEDATE]>'03 15 2008 12:00AM') AND [c].[SALEDATE]<='03 26 2008 12:00AM'))
| |--Clustered Index Seek(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [a]), SEEK:([a].[STORE]=[c].[STORE]), WHERE:(([a].[ITEM_ID]=1 AND [a].[PRICE]<>0.00) AND [a].[FLOWNO]=[c].[FLOWNO]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [b]), SEEK:([b].[STORE]=[c].[STORE]), WHERE:(([a].[ITEM_ID]<>[b].[ITEM_ID] AND [b].[PRICE]<>0.00) AND [b].[FLOWNO]=[c].[FLOWNO]) ORDERED FORWARD)
2.15的查询执行结果
|--Parallelism(Gather Streams, ORDER BY:([Expr1003] DESC))
|--Sort(ORDER BY:([Expr1003] DESC))
|--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1011])))
|--Hash Match(Aggregate, HASH:([a].[STORE], [b].[ITEM_ID]), RESIDUAL:([a].[STORE]=[a].[STORE] AND [b].[ITEM_ID]=[b].[ITEM_ID]) DEFINE:([Expr1011]=COUNT(*), [a].[ITEM_ID]=ANY([a].[ITEM_ID])))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [b].[ITEM_ID]))
|--Hash Match(Aggregate, HASH:([a].[STORE], [b].[ITEM_ID], [a].[FLOWNO]), RESIDUAL:(([a].[STORE]=[a].[STORE] AND [b].[ITEM_ID]=[b].[ITEM_ID]) AND [a].[FLOWNO]=[a].[FLOWNO]) DEFINE:([a].[ITEM_ID]=ANY([a].[ITEM_ID])))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [b].[ITEM_ID], [a].[FLOWNO]))
|--Hash Match(Inner Join, HASH:([c].[STORE], [c].[FLOWNO])=([b].[STORE], [b].[FLOWNO]), RESIDUAL:(([b].[STORE]=[c].[STORE] AND [b].[FLOWNO]=[c].[FLOWNO]) AND [a].[ITEM_ID]<>[b].[ITEM_ID]))
|--Bitmap(HASH:([c].[STORE], [c].[FLOWNO]), DEFINE:([Bitmap1010]))
| |--Hash Match(Inner Join, HASH:([c].[STORE], [c].[FLOWNO])=([a].[STORE], [a].[FLOWNO]), RESIDUAL:([a].[STORE]=[c].[STORE] AND [a].[FLOWNO]=[c].[FLOWNO]))
| |--Bitmap(HASH:([c].[STORE], [c].[FLOWNO]), DEFINE:([Bitmap1009]))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([c].[STORE], [c].[FLOWNO]))
| | |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY].[PK__BUY__4282C7A2] AS [c]), WHERE:(([c].[AMOUNT]>0.00 AND [c].[SALEDATE]>'02 15 2008 12:00AM') AND [c].[SALEDATE]<='03 26 2008 12:00AM'))
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[STORE], [a].[FLOWNO]), WHERE:(PROBE([Bitmap1009])=TRUE))
| |--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [a]), WHERE:([a].[ITEM_ID]=1 AND [a].[PRICE]<>0.00))
|--Parallelism(Repartition Streams, PARTITION COLUMNS:([b].[STORE], [b].[FLOWNO]), WHERE:(PROBE([Bitmap1010])=TRUE))
|--Clustered Index Scan(OBJECT:([OmeyInfo].[dbo].[BUY_DETAIL].[PK__BUY_DETAIL__455F344D] AS [b]), WHERE:([b].[PRICE]<>0.00))
我是想要知道为什么会出现这样的情况。是特定的数据分布、还是别的什么原因。
另外,现在saledate是允许为空的。如果设置为不为空,则这个情况消失。
datediff(dd,字段,'2008-01-01')>=0
and
datediff(dd,字段,'2008-01-10')<=0 --就可以了,看执行计划也没看出原因
不过Nested Loops应该是对排序过的数据做匹配,Hash Match应该是对先生成哈希表再匹配,为什么会用到这样两种方式呢?
1、buy表STORE, FLOWNO两字段的重复率,或者简单点,贴出
select count(distinct STORE),count(distinct FLOWNO) from buy
的结果2、buy表数据量3、试一下加索引 buy(saledate,amount),看有没有效果
Haiwer:谢谢。问题很好解决,我是想知道两种执行方式的不同。
zjcxc: 谢谢。能否解释一下什么时候会用Nested Loops,什么时候会用Hash Match?