Sum query take too much time

Question

Below query take approx 19 seconds to fetch 7877 records. I want to reduce time.

SELECT 
dvm.pk_user_id,
dvm.UID, 
IFNULL((SELECT SUM(taskassign.vreward_points) FROM tbl_task_master AS tasks LEFT JOIN tbl_task_assign_details AS taskassign ON tasks.pk_task_id = taskassign.fk_task_id WHERE taskassign.vtaskstatus = '1' AND dvm.pk_user_id = taskassign.fk_user_id GROUP BY taskassign.fk_user_id),0) AS earning_reward_points 
FROM tbl_digital_volunteer_master AS dvm 
ORDER BY earning_reward_points DESC,
pk_user_id ASC

There are loads of ways to optimise t-sql, and each of them depend very heavily on the DATA you've got. Take a look at https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-temporary-table-or-ta?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa to see a few possibilities that can help improve the performance. (I'm not spending more time finding a better link :P ) — Dan Rayson, May 31 '18 at 11:00
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) In addition, as well as CREATE TABLE statements for ALL relevant tables, questions about query performance ALWAYS require the results of the EXPLAIN for the given query. — Strawberry, May 31 '18 at 11:00

score 0 · Answer 1 · answered May 31 '18 at 10:41

How about doing the same but with a join

SELECT 
dvm.pk_user_id,
dvm.UID, 
IFNULL(t.earning_reward_points,0) AS earning_reward_points 
FROM tbl_digital_volunteer_master AS dvm 
LEFT JOIN (
    SELECT taskassign.fk_user_id, SUM(taskassign.vreward_points) AS earning_reward_points 
    FROM tbl_task_master AS tasks 
    LEFT JOIN tbl_task_assign_details AS taskassign ON tasks.pk_task_id = taskassign.fk_task_id 
    WHERE taskassign.vtaskstatus = '1' 
    GROUP BY taskassign.fk_user_id
) AS t ON  dvm.pk_user_id = t.fk_user_id 
ORDER BY earning_reward_points DESC, pk_user_id ASC

Sum query take too much time

1 Answers1