CODEWITHSUNDEEP
oracle-databaseselectsubquerywhere-clause
Denis Samadov
Denis Samadov

Reputation: 121

How to speed up this oracle select query?

I have following sql query in code, which is makes query go slow: 

SELECT a.*,
         (SELECT CASE
                    WHEN (score IS NOT NULL OR comments IS NOT NULL)
                    THEN
                       ' ( score : ' || TO_CHAR (SCORE) || ' )'
                    ELSE
                       ' ( hələ )'
                 END
            FROM t_lecture_task_present b
           WHERE b.t_idx = a.t_idx AND B.STUDENT_ID = '{$member_code}')
            AS task_score
    FROM t_lecture_task a
   WHERE a.lec_open_idx = '24422'
ORDER BY s_date ASC, t_idx ASC

(16 seconds)

If I try query without 

(SELECT CASE
           WHEN (score IS NOT NULL OR comments IS NOT NULL)
           THEN
              ' ( score : ' || TO_CHAR (SCORE) || ' )'
           ELSE
              ' ( hələ )'
        END
   FROM t_lecture_task_present b
  WHERE b.t_idx = a.t_idx AND B.STUDENT_ID = '{$member_code}')
 as task_score

it works faster.

So, I tried removing or comments is not null, and it works got 2 seconds faster.

Upvotes: 0

Views: 123

Answers (1)

Ronnis
Ronnis

Reputation: 12833

You can't compare the performance of queries that produce different results :)

Depending on data distribution in your tables, you would likely benefit from these indexes:

t_lecture_task(lec_open_idx) 
t_lecture_task_present(t_idx, student_id)

Try to re-write your query to use joins instead of scalar sub queries (select as a column). Not only are they more awkward to read, they are more difficult to optimize.

select a.*
      ,case when score    is not null 
              or comments is not null then ' ( score : ' || to_char (score) || ' )'
                                      else ' ( hələ )'
        end as task_score
  from t_lecture_task a
  left join t_lecture_task_present b on(
       b.t_idx      = a.t_idx
   and b.student_id = '{$member_code}')
  )
 where a.lec_open_idx = '24422';

Upvotes: 1

Related Questions