How to optimize nested query

Question

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

Is that five million or 50 million records?

Marcelo Cantos
– Marcelo Cantos

2010-11-25 11:41:55 +00:00
Commented Nov 25, 2010 at 11:41 — Marcelo Cantos
– Marcelo Cantos, Commented Nov 25, 2010 at 11:41

il_guru · Accepted Answer · 2010-11-25 11:43:39Z

1

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

answered Nov 25, 2010 at 11:43

il_guru

8,5483 gold badges46 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dr. Rajesh Rolen Over a year ago

@il_guru_ji: gr8.. thanks this improved my query performence.

Julien · Accepted Answer · 2010-11-25 11:43:46Z

0

You can try de-normalizing a little :

Create replies, NewPost and NotClear fields
Write a routine that updates these fields, cron it (period depends on 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...)

answered Nov 25, 2010 at 11:43

Julien

5253 silver badges9 bronze badges

Comments

bleeeah · Accepted Answer · 2010-11-25 11:46:20Z

0

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?

answered Nov 25, 2010 at 11:46

bleeeah

3,60422 silver badges25 bronze badges

1 Comment

RLT Over a year ago

You'll need a group by on the nested query for your SUMs

Collectives™ on Stack Overflow

How to optimize nested query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related