Help me！海量数据多表联合查询的问题！！

[guxincao11]

现有一个文章数据库，要实现查询指定记录数的数据（如select top 200），又要查询与其相关的表，最后实现输出指定记录的功能。（问题有点长，但是描述很详细，希望高手耐心看完并给与解答，谢谢！）
与之相关的数据库结构如下：

作品表：books（bookid<作品ID>,btitle<作品名称>,classid<作品类别ID>,hits<作品点击数>）记录数 万级
分卷表：bookj（jid<分卷ID>,jtitle<分卷名称>,bookid<分卷所属作品ID>） 记录数 十万级
章节表：bookz（zid<章节ID>,ztitle<章节名称>,bookid<章节所属作品ID>,jid<章节所属分卷ID>,updates<本章更新时间>） 记录数 百万级
分类表：class（classid<分类ID>,classname<分类名称>）

（主键、索引都是默认id，递增<如bookid，jid，zid，classid 都是主键和索引？>，其他字段均未做任何索引或优化等）

具体描述：要查询按点击数（hits）降序的前200部作品，并显示

作品名称（btitle），所属分类（classname，class.classid=books.classid），该作品最新章节（ztitle，bookz.bookid=books.bookid，按updates降序），最新章节所属分卷（jtitle，bookj.jid=bookz.jid）

要求输出格式如下（类似）：

分类名称 作品名称 分卷名称 章节名称 点击数
TestClass1 TestBook1 Testjuan1 TestChap1 100
TestClass2 TestBook2 Testjuan2 TestChap2 90
TestClass3 TestBook3 <NULL> <NULL> 80
TestClass4 TestBook4 Testjuan3 TestChap3 70

其中Books表里面的内容，对应的Boosj和Bookz里面可能找不到内容（因为该作品还没有章节），但是又要求输出（即使是空值），这样的话，我原先的sql语句（在底下）就不行了（因为过滤了空记录），我不知道left join 或者right join怎么用……

<----
原先用的是，嵌套循环，即，查询作品->查询所属分类->查询最新章节->查询其所属分卷，
用while循环，嵌套查询，输出（我用asp编写的）。
发现当要显示的记录数较大（200）的时候，速度很慢，效率太低（mssql占用cpu近100％，几乎死掉，最后用时近30秒），不得不放弃了。还有，随着要查询的记录增大，耗时几乎平方级增加！

然后改写后，大致如下
select top 200 a.bookid,a.btitle,a.classid,a.hits,b.jtitle,b.jid,c.ztitle,d.classname from books as a join bookj as b on a.bookid=b.bookid join bookz as c on a.bookid=c.bookid join class as d on a.classid=d.classid where zid = (select top 1 zid from bookz as c where c.bookid=a.bookid order by c.updates desc,c.zid desc) and b.jid = c.jid order by a.hits desc

---->

当查询较大记录时（同样200），效率提高很多（用时3－4秒），而且不管要查询的记录数多少，时间都差不多。

已经优化不少了，但是个人觉得远远不够！！（因为结果是秒级，而不是毫秒级的~~）我对sql语句研究不深，以上代码仅仅是经过多番网上搜索相关资料总结得来的，现学现用的，应该存在不少问题或者弯路。我发现没有经过系统的学习，自行泛泛搜索、研究是一件极度不明智的行为。有时候高手的一句有针对性的指点可以事半功 N 倍！！！

还有，因为要做个排行榜，所以不只是按照hits排序，也有其它的如favs(收藏数)排行，只是我没有列举而已。查询后生成Html（总共14个文件，就是14个排行榜），判断后，用For Next循环14次，总耗时 75 秒 左右！！我觉得效率太低了，我知道关键就是那个很长的sql语句select那里很费时（那个For Next具体实现方法不在讨论范围），但是又不知如何优化？？？

因此，在此恳请高手指点一下，如何设计代码，或者改动数据库，可以符合我的要求，以及效率可以更高一些？？用mssql存储过程最好。而且最好能给出asp的调用代码（我用asp写的程序）

[guxincao11]

To：zhangshenghua(张胜华)

很晚了，明天再测试你的方法，先谢过……

睡觉去了^_^

[LouisXIV]

你把现存索引都改为降序试试，并把books表的索引的hits改为前导列，其后再排bookid等等

[guxincao11]

To：zhangshenghua(张胜华)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
初步测试了你的方法，发现会有作品重复（所影响的行数为 217 行），可能是因为有章节的updates时间是相同的……
还有，我今天研究了一天的代码，跟你的比较了一下（一起执行），发觉貌似我的效率更高一些？（20多万章节，占用数据库空间1.7G！总时间2秒<查询分析器右下角得到的，我不知道在哪里看，汗！>）

我的：查询1：查询成本（相对于批处理）：14.43%
你的：查询2：查询成本（相对于批处理）：85.57%

只是在我的代码中，假如最新章节为空（新书，未有任何章节），则该作品不显示（与我想要的结果稍有出入）

[guxincao11]

To：Athoncj(阿龙)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
你的方法还未测试，明天再看，很晚了，睡觉先……^_^

[zhangshenghua]

若更新时间相同，则取zid最大的（经复杂运算后，必须在最后重新排序，因此末尾加了order by子句）：

select c.classname, t.btitle, j.jtitle, z.ztitle, t.hits
from (select t.bookid, t.btitle, t.classid, t.hits, max(convert(varchar, z.updates) + z.zid) updates_zid
from (select top 200 bookid, btitle, classid, hits from books order by hits desc) t
left join bookz z on z.bookid = t.bookid
group by t.bookid, t.btitle, t.classid, t.hits) t
left join bookz z on t.bookid = z.bookid and t.updates_zid = (convert(varchar, z.updates) + z.zid)
left join bookj j on z.bookid = j.bookid and z.jid = j.jid, class c
where t.classid = c.classid
order by t.hits desc;


我能写出的最快、最简单、最紧凑的，且完全符合要求的代码就是这样，没有优化空间了。
不要同时两个大规模查询，这样后执行的一定吃亏，因为系统未来得及释放被第一个查询消耗的资源。


自动计算一个查询的时间消耗（毫秒级）：

declare @d as datetime;
declare @c as int;
select @d = getdate();

//执行查询SQL；

select @c = datediff(ms, @d, getdate());
select @c milliseconds;

[zhangshenghua]

另外，别忘了我要你加的两个索引。
如果还不能满足要求，只能重新设计表结构和关系了，这个我就不帮你啦（收费，嘿）。

[wfliu]

好长

[zhangshenghua]

靠，帖子也不来结了？
帮你解决问题了，完了还要说我的比你的那个还慢，I服了YOU。

拉你进黑名单。

[guxincao11]

To:zhangshenghua(张胜华)
=================================
靠，帖子也不来结了？
帮你解决问题了，完了还要说我的比你的那个还慢，I服了YOU。

拉你进黑名单。
=================================
这话有些严重了。。。
问题还是没有很好地解决啊。。。你的的确比我的要慢的，我也不知道怎么回事。。。
没时间仔细测试了，还有别的东西要做。。。
很感谢你了！！！不知道我有多少分，怎么给:( 我在CSDN没混过多久，大多只是看贴，自己很少发文和回帖的。。。不好意思了。。。随便给你一些分吧。。。
帖子就先不结了（其实我也不怎么懂得结贴……），看看还有没有人有更好的思路的。。。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
看了一下，我只有100分:( 给你们70分了，手头留30分以备不时之需 :)

[guxincao11]

老大，怎么结贴或者给分啊？？？再试一次，晕了。。。

[guxincao11]

靠，不知怎么给分，哪位教教我？？？

[zhangshenghua]

1）从SQL语句的执行流程分析，你的不可能更快，尽管你那个忽略了没有章节的书（我的符合要求，没有忽略，这会增加一些开销），因为你是最后才排序取前200的；
2）查询策略与索引紧密相关，我的查询要求你加两个索引，你加了没有？加与不加差别可大了；
3）我也不知道怎么给分（从没问过问题，只回答过问题）；

[guxincao11]

To:zhangshenghua(张胜华)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
1）从SQL语句的执行流程分析，你的不可能更快，尽管你那个忽略了没有章节的书（我的符合要求，没有忽略，这会增加一些开销），因为你是最后才排序取前200的；
2）查询策略与索引紧密相关，我的查询要求你加两个索引，你加了没有？加与不加差别可大了；
3）我也不知道怎么给分（从没问过问题，只回答过问题）；
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
我现在的不是上面我给出的那个了，又经过一些自认为的优化，反正最后查询都会稍快一些的。。。我也不知道哪里的问题。。。索引我应该加了的（就是不知加得对不对:( ）
感谢你的帮助，现在已经没空在这个问题上面纠缠太久。。。再次感谢！！！

[zhangshenghua]

把你的“优化”贴出来啊~~~~~~牛皮佬！
你能写出更快的，我就跟你混了，NND。

[zhangshenghua]

借口不结帖，在CSDN可能不受欢迎。这是别人说的，我个人不在意（除了最近一个月，我很少在CSDN混，尽管已经注册好几年了）。

[guxincao11]

靠，今天在百度搜了老半天，终于知道怎么结帖了

[guxincao11]

To:zhangshenghua(张胜华)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
把你的“优化”贴出来啊~~~~~~牛皮佬！
你能写出更快的，我就跟你混了，NND。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
晕了，今天进来转转，竟然发现你又不满了。。。不知我哪里得罪你了，你说话如此“损”……
这个代码已经没有用了，因为我重新设计了数据库，把最新章节存入books表了，免得每次都查询那个海量表，毕竟增加章节不是特别频繁，一天有个几十上百的就不错了。。。
至于我以前的思路，我也不知道是什么思路，具体代码也不贴了，免得怡笑大方。
我也没说你的方法就不好了，其实是我还不是很理解你的思路。。。当时只好按照自己的思路随便写了——（都是菜鸟的理解，你是高手看了一笑而过吧。。）
我吹牛皮？算了，算我吹牛皮了。。。讨论这个已经没有意义了，麻烦你“大人有大量”，不要再计较了，OK？
我是菜鸟，希望能跟各位前辈高手多学学。。。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
最后告诉大家一声，其实 zhangshenghua(张胜华) 是高手，而且很热心，虽然他对我有意见。。。只要你的脸皮可以像我一样厚，不对某些人的某些话在意，你是可以学到很多的。。。。

[jianqiliu999]

呵呵，都是道上混的，谅解谅解！

[guxincao11]

要分你就拿去吧。。。。
原以为我是初学者，来CSDN学习的，没想到，很多人眼睛就盯着那些分，那些很虚的东西。。。
我故意不结帖？我有必要吗？不结帖貌似会被强制结帖并扣什么信誉分的（我刚刚知道）。。虽然我都不在意这些。。。
算了，自己没技术，怪不得别人。。。
再次感谢你们这些高手的指点！！

[tigerjacky]

mark