Topic: Interesting MS SQL server performance results @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / Interesting MS SQL server performance results -deep_blue(BLUE); 2007-8-24 {1134} (#3890619@0)

must be very interesting.... however it is friday afternoon now..... -win(秋天的菠菜); 2007-8-24 (#3890627@0)
试过 NOT EXISTS 了吗？ -newkid(newkid); 2007-8-24 (#3890685@0)
how did you get the time? 如果你用SET STATISTICS TIME ON会比较准确些。很多情况Server本身没太大工作量，时间主要花在数据返回的过程中（包括网络传输，用户端显示），如果看总时间的话，不太能区分query的好坏。 -bdbs(不多不少); 2007-8-24 (#3891133@0)

Good point. How to show cup time in SELECT query? -deep_blue(BLUE); 2007-8-27 (#3894902@0)

Under SQL Query Analyzer (Or Management Studio), before SELECT query, turn on the STATISTICS TIME. You will see the detail CPU time in Messages tab.SET STATISTICS TIME ON

SELECT ItemID FROM TABLE_TWO WHERE ItemID NOT IN (SELECT ItemID FROM TABLE_ONE) -bdbs(不多不少); 2007-8-27 {105} (#3895003@0)

很有趣的测试，说明两点： 1 sql server会优化sql，结果基本一样，所以sql语句不用考虑优化。 2 index对于not in 没有用，server始终是用表扫描。 -googlebot(bot); 2007-8-24 (#3891136@0)

简单的一个通过subquery从metadata中拿一个常数作运算，很神奇么？不管如何，说明依靠Server作优化基本行不通，说“sql语句不用考虑优化”基本是误导。:) -bdbs(不多不少); 2007-8-24 (#3891177@0)
你不用抬杠，server又不是万能，什么语句都认识，我们优化的原则是尽量避免表扫描。 -googlebot(bot); 2007-8-24 (#3891184@0)

你的认识还停留在"FULL TABLE SCAN IS EVIL"的误区。用索引有时候读盘次数更多。楼主碰到的这种有趣的现象在ORACLE中是看不到的。如果用索引不合算，优化器将会忽略索引，并不会强行使用而降低PERFORMANCE -newkid(newkid); 2007-8-25 (#3892437@0)

呵呵，还在推销你的SP。我这个例子可不是用在web application的。只能是table or view。 -bdbs(不多不少); 2007-8-27 (#3895009@0)

不错，又快了一些。本来很简单的一个query，没想得那么复杂。现在看来，即使一个简单的query，还是有很多油水可以榨的。 -bdbs(不多不少); 2007-8-27 (#3895141@0)

2 index对于not in 没有用，server始终是用表扫描。 -hard20(hard20); 2007-8-24 (#3891142@0)
如果ItemID是整数，我建议作另外一个实验，改成varchar类型, 在测试一下结果。 -googlebot(bot); 2007-8-24 (#3891273@0)

by sql1,2 in test result set 1 and set 2, I am guessing that sequencial scan is faster than using index in your invironment. -647i(流浪的步行万里); 2007-8-24 (#3891337@0)
More interesting thingMore interesting thing is that when I apply Display Estimated Execution Plan to Q1 and Q2. It gives exactly same plan:
Starting from scanning both tables, then Hash Match/Left Anti Semi Join, finally SELECT operation.

It clearly shows that database server does have ability to optimize query, at least to those not too complicated query.

Of course, the server is not superpower. When in some complicated tasks, experienced developers are more intelligent then the machine. -deep_blue(BLUE); 2007-8-27 {484} (#3894962@0)

你的QUERY A 和 B 用的时间基本一样，可以猜想计划也一样，有什么INTERESTING的？难道你是说加了索引后，执行计划也一样，但是时间变长了？ -newkid(newkid); 2007-8-27 (#3895020@0)

I'm still testing. Results may be diffrent. -deep_blue(BLUE); 2007-8-27 (#3895070@0)
CPU spending for indexed and non indexed queriesPer BDBS’s suggestion, I put ‘SET STATISTICS ON’.

Because I noticed that in my previous results, in indexed query it returned sorted result (although without ORDER BY clause), but non-indexed query returned unsorted rows. Therefore, ORDER BY clause is added in query.

After redoing the Q1, results show indexed query spends less cpu time, (about 56.2%) but more elapsed time (about 136%) -deep_blue(BLUE); 2007-8-27 {399} (#3895132@0)

elapsed time要选多次运行的平均值才作数，因为Server和Network等在不同时间会有不同的忙的状态。如果大多数情况Q1的total elapsed time都比Q2长，这个问题就会比较有意思了，得好好查查了。 -bdbs(不多不少); 2007-8-27 (#3895145@0)
有没有磁盘IO的统计数字？我猜用了索引后IO更多。尽量在只有一个用户的环境下做实验；在运行不同的查询之前把CACHE清理干净，这样才比较客观。 -newkid(newkid); 2007-8-27 (#3895232@0)

看来我没猜措，physical reads增加了那么多。不明白MS SQL的优化器为什么还是选用了索引。 -newkid(newkid); 2007-8-27 (#3895468@0)

没看懂。没有索引，查询要scan table，却没有读硬盘？另外选用索引也许是因为磁盘IO性能可以提高，例如硬盘缓存、内存缓存甚至RAID等。 -liquid(avaya); 2007-8-27 (#3895539@0)

虽然我不用SQLSERVER,但GOOGLE可知Read-Ahead Reads也是读盘操作。不管你磁盘如何优化，开销就是开销，避免读盘就可以减少开销。有趣的是，某文章说在优化查询时应该忽略physical read,因为那是DBA的事，logical reads才重要，难怪MS的优化器会选索引呢，哈哈。 -newkid(newkid); 2007-8-27 {122} (#3895648@0)

TABLE TWO上的索引如果不去掉，会影响速度吗？如果你有不同的报表，需要运行不同的查询，ITW给出的建议有矛盾怎么办？如果建立一个新索引可能会降低其他查询的效率，这MS的优化器也太那个了。 -newkid(newkid); 2007-8-29 (#3900566@0)

1. if itemID is vchar, do NOT use cluster index .2. return too many rows - try to avoid this in real solution design,if return 120k of 150k clustered index will not be used, non-clustered index maybe used because of small row size (only itemid is returned, like covering index) -bohu(利人不损己常开心); 2007-9-21 {161} (#3947938@0)

@Florida