How to optimize nested query

i have got lot of queries which are written like:

     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

Please tell me how to optimize and better way to write such queries. because i have got tables with records 5,00,000. so it takes lots of time and some times gets time out.

Thanks

You should group the variuos subquery in a single one with different count and use a join to put the data together

the subquery should be:

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

While the finalquery is the following

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

You can try de-normalizing a little :

1. Create replies, NewPost and NotClear fields
2. Write a routine that updates these fields, cron it (period depends on 3.)
3. Rewrite most/all queries that impact these fields to update them. If you rewrite all, run 2. a few times a day. Otherwise, depending of data integrity you need, a few times an hour.

This will clearly help with your query. However, it needs more maintenance as any small query, almost never used, can break consistency (think of some moderating tools for BBs like split topic...)

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

I have not been able to test this, so some syntax errors may exist, but you get the drift?