CODEWITHSUNDEEP
sqlsql-server-2005query-optimization
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

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

Upvotes: 0

Views: 2048

Answers (3)

bleeeah
bleeeah

Reputation: 3604

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?

Upvotes: 0

Julien
Julien

Reputation: 525

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...)

Upvotes: 0

il_guru
il_guru

Reputation: 8508

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

Upvotes: 1

Related Questions