代码之家 › 专栏 › 技术社区 › Dr. Rajesh Rolen

如何优化嵌套查询

query-optimization sql-server-2005 sql

Dr. Rajesh Rolen · 技术社区 · 15 年前

我有很多问题都是这样写的:

     select  thread_id as topic_id,title as topic            
  ,            
    isnull((select count(*) from tblmessages b where thread_id=a.thread_id and is_approved='Y' and sort_level>1            
     group by b.thread_id            
     ),0) as replies,            
    isnull((select count(*) from tblmessages b where thread_id=a.thread_id and isnull(new_post,'Y')='Y' and sort_level>1            
     group by b.thread_id            
     ),0) as NewPost,            
    isnull((select count(*) from tblmessages b where thread_id=a.thread_id and isnull(is_approved,'N')='N' and sort_level>1            
     group by b.thread_id            
     ),0) as NotClear,            

    sort_level,sort_index,  from tblMessages a            
    where   sort_level=1 and category=@category 
    order by topic_id desc

请告诉我如何优化和更好的方式来编写这样的查询。因为我有50万张记录表。所以这需要很多时间,有时也需要时间。

谢谢

3 回复 | 直到 15 年前

il_guru 0xAX 15 年前

您应该将variuos子查询分组为一个具有不同计数的子查询,并使用联接将数据放在一起

子查询应为:

select thread_id
     count(when isnull(is_approved,'N')='N' then 1 end) as replies,
     count(when isnull(new_post,'Y')='Y' then 1 end) as NewPost,
     count(when isnull(is_approved,'N')='N' then 1 end) as NotClear
     from tblmessages 
     where sort_level>1  
     group by thread_id

而finalquery是

select  thread_id as topic_id,title as topic,
   sort_level,sort_index , B.replies, B.NewPost, B.NotClear
   from tblMessages a            
   join
     (select thread_id
     count(when isnull(is_approved,'N')='N' then 1 end) as replies,
     count(when isnull(new_post,'Y')='Y' then 1 end) as NewPost,
     count(when isnull(is_approved,'N')='N' then 1 end) as NotClear
     from tblmessages 
     where sort_level>1  
     group by thread_id) as B
     on a.thread_id = B.thread_id
    where   sort_level=1 and category=@category 
    order by topic_id desc

Julien 15 年前

您可以尝试稍微反规范化:

创建 replies , NewPost 和 NotClear 领域
编写一个更新这些字段的例程, cron 它(周期取决于3。)
重写影响这些字段的大多数/所有查询以更新它们。如果全部重写,请运行2。一天几次。否则,根据需要的数据完整性,一小时几次。

这显然有助于您的查询。然而,它需要更多的维护,因为任何小的查询,几乎从未使用过,都会破坏一致性(想想BBs的一些调节工具,比如split topic…)

bleeeah 15 年前

SELECT a.* 
FROM 
    (SELECT   
        thread_id AS topic_id,
        title AS topic ,
        SUM(CASE WHEN is_approved='Y' AND sort_level > 1 THEN 1 ELSE 0 END) as replies, 
        SUM(CASE WHEN isnull(new_post,'Y')='Y' AND sort_level > 1 THEN 1 ELSE 0) END as NewPost, 
        SUM(CASE WHEN isnull(is_approved,'N')='N' AND sort_level > 1 THEN 1 ELSE 0 END) as NotClear, 
        sort_level ,
        sort_index,  
        category ,
        topic_id
    FROM 
        tblMessages 
    ) a
WHERE 
    a.sort_level=1 AND a.category=@category 
ORDER BY 
        a.topic_id DESC

我还不能测试这个,所以可能存在一些语法错误,但是你得到了漂移吗?