Why grouping in a subquery causes problems

Question

When I include the 2 commented out lines in the following subquery, seems that it takes forever until my Sybase 12.5 ASE server gets any results. Without these 2 lines the query runs ok. What is so wrong with that grouping?

select days_played.day_played, count(distinct days_played.user_id) as OLD_users
from days_played inner join days_received
on days_played.day_played = days_received.day_received
and days_played.user_id = days_received.user_id
where days_received.min_bulk_MT > days_played.min_MO
and days_played.user_id in

(select sgia.user_id 
from days_played as sgia
where sgia.day_played < days_played.day_played
--group by sgia.user_id 
--having sum(sgia.B_first_msg) = 0
)

group by days_played.day_played

Answer 1

Find out what the query does by using showplan to show the explanation.

In this case Can't you eliminate the subquery by making it part of the main query?

Answer 2

Could you try rewriting the query as follows?

select days_played.day_played,
       count(distinct days_played.user_id) as OLD_users
  from days_played
 inner join days_received on days_played.day_played = days_received.day_received
                         and days_played.user_id = days_received.user_id
 where days_received.min_bulk_MT > days_played.min_MO
   and 0 = (select sum(sgia.B_first_msg)
              from days_played as sgia
             where sgia.user_id = days_played.user_id
               and sgia.day_played < days_played.day_played
            )
 group by days_played.day_played

I guess this should give you better performance...

Answer 3

ok I found out what the problem was I had to include user id in the subquery: "where days_played.user_id = sgia.user_id and sgia.day_played < days_played.day_played"