作者：姜敏 来源：博客园 酷勤网收集 2008-05-14
MSDN对子查询的定义是这样的:
可以将一个查询的结果用作另一个查询的输入。可以将子查询的结果用作使用 IN( ) 函数、EXISTS 运算符或 FROM 子句的语句。
一条好的值得称赞的规则是尽量用连接代替所有的子查询。优化器有时可以自动将子查询“扁平化”，并且用常规或外连接代替。但那样也不总是有效。明确的连接对选择表的顺序和找到最可能的计划给出了更多的选项。当你优化一个特殊查询时，了解一下是否去掉自查询可产生很大的差异。这段话出自http://www.innovatedigital.com/htm_speek/SQLServerOpt.shtml它显然告诉我们在查询的时候最好不要用子查询，当时我并不太在意，至到我有一次遇到了这样的情况。
现在的网站主要的压力都来自于数据库，频繁的数据库访问经常会使服务器死机。我的原则就是尽量减少数据库的连接，能一次性取出的决不多连接数据库一次，但是有时候并不完全是这样。郁闷，无解。
下面一条SQL语句里面有一个子查询,info.RESOURCE_VOLUMECOUNT,它是指一个电影共有多少集.这个字段是int类型的.子查询的目的就是计算出一个电影在资源表中总共有多少集(实际存在的), 两者做减法操作就可以计算出这个电影共缺多少集（lastCount）我们知道在这条语句执行的时候,外层记录查询一次在计算 lastCount字段的时候又要查询表:电影表一次.如果最外层有10条记录,那么执行这次查询一共要扫描电影表11次,连接数据库1次.
基于有的朋友看不明白我写的SQL语句,可能是因为我写的有些字段和本案例没有太大关系的原因,现在特将它们做一个替换.
string SqlCode = @"select distinct info.resource_officialname , info.resource_id, 字段1, ( info.RESOURCE_VOLUMECOUNT - (select count(vid3.resource_id ) from 电影表 info3 inner join TB_RESOURCE_PRIMARVIDEO vid3 on info3.resource_id =vid3.resource_id and info.resource_id =vid3.resource_id ) ) as lastCount, 字段2 from 电影表 info where 1=1 ";
现在换一种方法来做:
下面的语句和上面的语句只有一个区别就是没有查询lastCount字段,但是查询出了info.RESOURCE_VOLUMECOUNT字段（完整电影应该有的集数）,就是说去掉了那个子查询.在绑定数据的时候,通过每次记录的resource_id(电影ID)重新进数据库执行一次以前的子查询,
具体实现是这样的:
在外层记录做循环的时候，通过resource_id来取电影实际存在多少集.在程序中来实现info.RESOURCE_VOLUMECOUNT与"电影实际存在的集数"做减法.这样做也是查询表:电影表总共11次,连接数据库11次.
private string getCount(string resource_id)
{
//返回结果 string s=""; string sql=@"select count(vid3.resource_id ) from 电影表 info3 inner join TB_RESOURCE_PRIMARVIDEO vid3 on info3.resource_id =vid3.resource_id and info3.resource_id ="+ resource_id; //执行代码省略 return s; 
} 下面是去掉了子查询的SQL查询语句。它是最外层的循环，每循环一条记录后用取得的电影ID再调用上面的方法去计算此电影实际有多少集，这样在功能上就与第一种方法(包含子查询)得到的结果是一样的了。但是在效率上有太大的差距。
string SqlCode = @"select distinct info.resource_officialname , info.resource_id, 字段1, info.RESOURCE_VOLUMECOUNT,--完整电影应该有多少集 字段2 from 电影表 info where 1=1 ";理论上说应该是查询表11次打开数据库连接1次的在性能上应该会好很多啊,但是实际不则相反,反而是查询表11次打开数据库11次之多的后一种方法在执行时间上会少很多.不知道这样的子查询在实际数据库操作中到底会有多大的实际用处呢，是否真是鸡肋呢？我在实验的时候当数据特别少时差别不大，一旦多了，哪怕只有1000条，性能上都有特别大的差距，有时会出现死机的状况。我用的是interBase下的firebird数据库，这里不能显示出执行的具体时间，抱歉了。虽然是小型的，但是也能反应出问题来，希望高手们看到了能帮我分析一下其中的原因，我们在查询数据的时候是否还能用这样的子查询呢？
本人的文章发布之后有很多朋友都给出了自己的想法,很多都不错,主要的一种说法就是利用join来替代子查询,可是如果说在取出的字段特别多的时候,进行分组时 group by 中会出现多个字段,这在性能上也未必会好.而且这只是一种解决方案,能不能以我文章中的性能问题分析一下具体原因呢?为什么第二种方案效率会更好呢?
Feedback
#1楼 2008-05-13 11:21 个人知识管理
1.查询表TB_RESOURCE_INFO总共11次,连接数据库11次：肯定是对数据库连接池没有理解，程序出错导致
2.子查询作为字段的值返回还是第一次看到，因为没有条件限制，本例不应用子查询，应直接用Join。必要时应引入“冗余字段”
#2楼 2008-05-13 11:23 PerfectDesign
楼主能不能将这两个语句的执行计划和IO，以及时间方面的统计贴出来，好对比对比，你这样还是没看懂啊
#3楼 2008-05-13 11:26 PerfectDesign
@个人知识管理
子查询是可以用作字段值的，只不过一般这种情况都会优化成连表查询。
不过不明白你说的 对数据库连接池没有理解，程序出错导致 什么意思.....
#4楼 [楼主] 2008-05-13 11:29 姜敏
@ 个人知识管理
1.查询表TB_RESOURCE_INFO总共11次,连接数据库11次：肯定是对数据库连接池没有理解，程序出错导致
请教这是什么意思啊？
#5楼 [楼主] 2008-05-13 11:33 姜敏
@ 个人知识管理
2.子查询作为字段的值返回还是第一次看到，因为没有条件限制，本例不应用子查询，应直接用Join
如果是join的话，那么在有统计函数count()的时候，不是还要进行分组吗？
如果查询的字段比较多， 那么group by 的字段就会特别多，这样在性能上也不会太好吧。
#6楼 [楼主] 2008-05-13 11:34 姜敏
@PerfectDesign
楼主能不能将这两个语句的执行计划和IO，以及时间方面的统计贴出来，好对比对比，你这样还是没看懂啊
哈哈，抱歉我用的是interBase数据库，好不能显示执行时间啊。
#7楼 2008-05-13 11:37 PerfectDesign
@姜敏
推荐使用索引视图来做
count函数还是可以应用索引视图的。
建议直接建一个索引视图
#8楼 2008-05-13 11:40 PerfectDesign
居然...................
#9楼 [楼主] 2008-05-13 11:44 姜敏
@PerfectDesign
推荐使用索引视图来做
count函数还是可以应用索引视图的。
建议直接建一个索引视图
您这种性能上的表现可否有解释呢？您的索引视图和我实现的方法是差不多的。
#10楼 [楼主] 2008-05-13 11:45 姜敏
@PerfectDesign
居然...................
这又是为何?
#11楼 2008-05-13 12:06 小强.假的 [未注册用户]
压力 确实 是数据库的频繁查询. 最好的办法是 缓存..
#12楼 2008-05-13 12:07 假正经哥哥
.....
-
sum(case vid3.resource_id is null then 0 else 1 end) as xx
....
from 电影表 info
left join TB_RESOURCE_PRIMARVIDEO vid3 on
info.resource_id =vid3.resource_id
试试上面，你前面引用的话不是的都说的很明白了吗？
一条好的值得称赞的规则是尽量用连接代替所有的子查询 ！！！
#13楼 2008-05-13 12:15 PerfectDesign
@姜敏
指的是你居然用interbase
#14楼 2008-05-13 12:19 brightwang
是不是鸡肋自己可以看看查询计划，没有绝对的好也没绝对的坏，只是应用场合不同
#15楼 [楼主] 2008-05-13 14:48 姜敏
@ 假正经哥哥
.....
-
sum(case vid3.resource_id is null then 0 else 1 end) as xx
....
from 电影表 info
left join TB_RESOURCE_PRIMARVIDEO vid3 on
info.resource_id =vid3.resource_id
你的关点和@个人知识管理的
2.子查询作为字段的值返回还是第一次看到，因为没有条件限制，本例不应用子查询，应直接用Join。必要时应引入“冗余字段”
是差不多的，但都有一个问题：
如果是join的话，那么在有统计函数count()的时候，不是还要进行分组吗？
如果查询的字段比较多， 那么group by 的字段就会特别多，这样在性能上也不会太好吧。
和它的理由是一样的。
#16楼 [楼主] 2008-05-13 14:49 姜敏
@ PerfectDesign
@姜敏
指的是你居然用interbase
哈哈，没办法，我也不想，但是现在小项目没办法。
#17楼 2008-05-13 15:57 假正经哥哥
@姜敏
实践是检验真理的唯一标准，如果字段多group by 效率不好而用子查询
那么 子查询做为字段来做的效率永远是最低的。
如果你要考虑group by的效率，那么你就该在适当的字段上建立，或者几个字段分开group 的方式 （嵌套）
#18楼 [楼主] 2008-05-13 16:36 姜敏
@ 假正经哥哥
子查询做为字段来做的效率永远是最低的
这句话有什么根据吗?
其实我想知道就是子查询做为字段和我用的第二种方法:循环进数据库取数据,这两方法在执行效率上的区别.
#19楼 2008-05-13 17:06 金色海洋（jyk）
要是我来实现这个功能的话，我会在 电影表里加至少两个和集数相关的字段。
电影表
电影一共有多少集
已经上传了多少集
其它字段
可能“电影一共有多少集”的字段已经有了，“已经上传了多少集”的字段好像没有，而是通过另一个表，动态count 出来的。
我会在每次变动的时候（比如上传了一集新的），来修改 “已经上传了多少集”的字段，这样，现实的时候就会很快了。
没有太细看，可能理解的不正确，仅供参考。
#20楼 [楼主] 2008-05-13 17:29 姜敏
@ 金色海洋（jyk）
非常谢谢您的解决方案,非常有效,但是您能不能给在下说下这个问题呢?
其实我想知道就是子查询做为字段和我用的第二种方法:循环进数据库取数据,这两方法在执行效率上的区别.
#21楼 2008-05-13 18:02 PerfectDesign
@金色海洋（jyk）
你这个想法就是索引视图的想法
而且不需要你手动维护聚合字段
#22楼 2008-05-13 21:41 金色海洋（jyk）
说实在的,你写的sql语句我都没有看懂，所以区别就不好说了。
count的效率是很慢的，他必须去“数数”，基本没有什么可以优化的方法，所以要尽量地少用，及使用的话，也要在where 条件里的字段做好索引。
再有就是看 到底做了多少次count 。可能第一种方法是对电影表里的所有记录都做了一遍count ，而第二种只做了11次count 。
#23楼 [楼主] 2008-05-14 09:24 姜敏
@ 金色海洋（jyk）
1:说实在的,你写的sql语句我都没有看懂，所以区别就不好说了。
2:count的效率是很慢的，他必须去“数数”，基本没有什么可以优化的方法，所以要尽量地少用，及使用的话，也要在where 条件里的字段做好索引。
3:再有就是看 到底做了多少次count 。可能第一种方法是对电影表里的所有记录都做了一遍count ，而第二种只做了11次count 。
现在本人针对您的观点提出点问题:
1:抱歉,写的不太好,现在修改了下应该可以看明白了.
2:count的效率是很慢的，他必须去“数数”，基本没有什么可以优化的方法，所以要尽量地少用
尽量少用是什么概念,有的地方是一定要用的,您是否有更好的计算总数的方法呢?
3:可能第一种方法是对电影表里的所有记录都做了一遍count ，而第二种只做了11次count 。
我第一种方法里面有子查询,所有外层循环一次就要count一次,所有它总共count了10次,第二种方法也是count10次,他们唯一的区别就是第一种方法只连接数据库1次,页第二种方法连接数据库10次而已.
本人的这两种方法理论上应该是第一种连接数据库次数少的在效率上应该会好,便是实际上是第二种,我想知道就是这两都之间产生性能差的原因.不知您这下有没有看明白我的问题呢?请指教.
#24楼 2008-05-14 10:21 金色海洋（jyk）
是不是应该先先写一下数据库的结构，就是表名，字段名、字段含义的介绍。
还有表之间的关联、关系，还有用意，要达到的效果。
你对这个项目是很了解的，但是你如何让其他人也能很容易的了解呢？
当然我应该先告诉你，我那些地方没有看明白，应该明确说明，但是不好意思，我懒了一下，只是说没有看明白，其实也是想看一下你会怎么理解，怎么做的能让大家看明白。
ps:如果只有我没有看明白的话，说明我比较笨:)。
对了建议你先看看 SQL Server 2005 技术内幕 T_SQL 查询 。虽然说的是2005，但是也包含了很的多 2000 的知识点，尤其是第一章，如果看明白之后，对你会很有帮助的。