Results 1 to 2 of 2

Thread: select speedup

  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: select speedup

    Hi,
    I have some select statement that is so slow that it's required to be optimized. Unforetunately I have at the moment no idea how. Select has 4 subselects and this is the reason why it works so long. But I donn't know how to avoid this subselects. I've tried with group by, but it was even slower and gave wrong results.
    Here is the query:
    select a.id,a.card_id,week(a.activity_date,1), dayofweek(a.activity_date),a.activity_date,
    (select sec_to_time(sum(time_to_sec(b.activity_duration))) from v_activity_info b where ((a.id = b.activity_id) and (b.activity = 0))) AS rest_time,
    (select sec_to_time(sum(time_to_sec(c.activity_duration))) from v_activity_info c where ((a.id = c.activity_id) and (c.activity = 1))) AS standby_time,
    (select sec_to_time(sum(time_to_sec(d.activity_duration))) from v_activity_info d where ((a.id = d.activity_id) and (d.activity = 2))) AS work_time,
    (select sec_to_time(sum(time_to_sec(e.activity_duration))) from v_activity_info e where ((a.id = e.activity_id) and (e.activity = 3))) AS drive_time,
    a.day_distance from activities a;

  2. #2
    Join Date
    Aug 2008
    Posts
    2
    Thanks to Pavel Vorontsov.
    the Answer is:
    select a.id
    ,a.card_id
    ,week(a.activity_date,1)
    ,dayofweek(a.activity_date)
    ,a.activity_date
    ,sec_to_time(sum(time_to_sec(case when b.activity = 0 then b.activity_duration end))) AS rest_time
    ,sec_to_time(sum(time_to_sec(case when b.activity = 1 then b.activity_duration end))) AS standby_time
    ,sec_to_time(sum(time_to_sec(case when b.activity = 2 then b.activity_duration end))) AS work_time
    ,sec_to_time(sum(time_to_sec(case when b.activity = 3 then b.activity_duration end))) AS drive_time
    ,a.day_distance
    from activities a inner join v_activity_info b on a.id = b.activity_id
    group by a.id
    ,a.card_id
    ,week(a.activity_date,1)
    ,dayofweek(a.activity_date)
    ,a.activity_date
    ,a.day_distance;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •