22,294
社区成员
发帖
与我相关
我的任务
分享百分求解SQL SERVER 2000中使用了top后查询速度明显变慢的问题
小弟项目中遇到特别奇怪的一个问题，百思不得其解，搜索了许多资料，也没有找到合适的答案。希望这里的高手能回答小弟这个问题，不胜感谢。
有三张表，一张人员表sys_emp，主键是emp_code，外键是comp_code。该表大约有5万条数据。一张单位表sys_company，主键是comp_code。该表大约800条数据。一张人员薪酬表sys_emp_salary，主键是year_month，emp_code。且salary_item1是工资项1，可为空，salary_item2是工资项2，可为空。该表大约有10万条数据。
原来的查询sql如下：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，花费1秒钟不到。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟。
后来需要在表sys_emp_salary增加一个[地区location]字段，并需要把该字段设置成主键。而人员表sys_emp本来就有location字段，但值都是空且不是主键，也需要把该字段设置成主键。因此，先在表sys_emp_salary增加一个允许空的[location]字段，默认值为'00'，然后再设置成主键。将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
此时，由于程序还未修改，若用原来的sql查询：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，要花费1分钟多。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
但将sql若加上关联条件，改成：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code and a.location=b.location left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，花费1秒钟不到。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
于是小弟纳闷了，按理说top 5的速度应该更快，为什么反而更慢了呢？求解，十分感激！
有三张表，一张人员表sys_emp，主键是emp_code，外键是comp_code。该表大约有5万条数据。一张单位表sys_company，主键是comp_code。该表大约800条数据。一张人员薪酬表sys_emp_salary，主键是year_month，emp_code。且salary_item1是工资项1，可为空，salary_item2是工资项2，可为空。该表大约有10万条数据。
原来的查询sql如下：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，花费1秒钟不到。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟。
后来需要在表sys_emp_salary增加一个[地区location]字段，并需要把该字段设置成主键。而人员表sys_emp本来就有location字段，但值都是空且不是主键，也需要把该字段设置成主键。因此，先在表sys_emp_salary增加一个允许空的[location]字段，默认值为'00'，然后再设置成主键。将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
此时，由于程序还未修改，若用原来的sql查询：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，要花费1分钟多。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
但将sql若加上关联条件，改成：
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code and a.location=b.location left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，花费1秒钟不到。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
于是小弟纳闷了，按理说top 5的速度应该更快，为什么反而更慢了呢？求解，十分感激！
...全文
请发表友善的回复…
发表回复
deadpanda 2010-10-25
- 打赏
- 举报
我查看了帮助，明白了成本的准确含义：
查询优化器执行此操作的成本（成本），包括此操作的成本占查询总成本的百分比。由于查询引擎选择最高效的操作执行查询或执行语句，因此该值应尽可能低。
查询优化器执行此操作及同一子树内位于此操作之前的所有操作的总成本（子树成本）。
但是我个人感觉这两个成本差异不算很大，可时间上差别那么大，具体消耗会是在哪里呢？
请求帮助，十分感谢！
[Quote=引用 4 楼 fredrickhu 的回复:]
引用 2 楼 billpu 的回复:
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
这个可能性是有的
location并不太适合做主键
查看执行计划看看 是哪里消耗得多
[/Quote]
查询优化器执行此操作的成本（成本），包括此操作的成本占查询总成本的百分比。由于查询引擎选择最高效的操作执行查询或执行语句，因此该值应尽可能低。
查询优化器执行此操作及同一子树内位于此操作之前的所有操作的总成本（子树成本）。
但是我个人感觉这两个成本差异不算很大，可时间上差别那么大，具体消耗会是在哪里呢？
请求帮助，十分感谢！
[Quote=引用 4 楼 fredrickhu 的回复:]
引用 2 楼 billpu 的回复:
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
这个可能性是有的
location并不太适合做主键
查看执行计划看看 是哪里消耗得多
[/Quote]
deadpanda 2010-10-25
- 打赏
- 举报
[Quote=引用 6 楼 abuying 的回复:]
加上and a.location=b.location ，因为location是主键，按照主键的索引当然快了。
将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
[/Quote]
可是这条sql没有加上条件，不加top 5 的话，只要1秒钟，我觉得这个与主键的索引没关系啊。
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，要花费1分钟多。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
加上and a.location=b.location ，因为location是主键，按照主键的索引当然快了。
将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
[/Quote]
可是这条sql没有加上条件，不加top 5 的话，只要1秒钟，我觉得这个与主键的索引没关系啊。
select top 5 * from sys_emp_salary a left join sys_emp b on a.emp_code=b.emp_code left join sys_company c on b.comp_code=c.comp_code where a.year_month='201009' and isnull(a.salary_item1,0)<isnull(a.salary_item2,0)
查询出5条记录，要花费1分钟多。若此时把 top 5 去掉，查询出7000条记录，花费1秒钟左右。
abuying 2010-10-21
- 打赏
- 举报
加上and a.location=b.location ，因为location是主键，按照主键的索引当然快了。
将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
将表sys_emp[location]字段的值设置为'00'，然后再设置成主键。
deadpanda 2010-10-21
- 打赏
- 举报
[Quote=引用 2 楼 billpu 的回复:]
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
[/Quote]
查看并对比了两个sql的执行计划，消耗成本分布的百分比是一样的，但是执行速度慢的sql在sys_emp这张表消耗的成本比执行速度快的sql多了0.0002左右的成本。请告诉我这个成本是什么意思？
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
[/Quote]
查看并对比了两个sql的执行计划，消耗成本分布的百分比是一样的，但是执行速度慢的sql在sys_emp这张表消耗的成本比执行速度快的sql多了0.0002左右的成本。请告诉我这个成本是什么意思？
--小F-- 2010-10-21
- 打赏
- 举报
[Quote=引用 2 楼 billpu 的回复:]
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
[/Quote]
这个可能性是有的
location并不太适合做主键
查看执行计划看看 是哪里消耗得多
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
[/Quote]
这个可能性是有的
location并不太适合做主键
查看执行计划看看 是哪里消耗得多
claro 2010-10-21
- 打赏
- 举报
不了解。很模糊。
billpu 2010-10-21
- 打赏
- 举报
另外有可能你的那个location并不太适合做主键(猜测),可比较性太少,重复字段太多,造成聚集索引效率下降,另外可能和sqlserver版本有关系,据说05之后因为有rowcount 关系,造成执行计划的复杂.还是建议你看下再说
billpu 2010-10-21
- 打赏
- 举报
具体你可以看看执行计划,看看消耗在了什么地方
