question

sindhu avatar image
sindhu asked

Query Optimization

Are there any ways of optimizing the attached query...For returning 200 records it takes 23 seconds.If I take out order by it runs fast,but I need order by.Ideas please.Thanks! SELECT top 200 ruc.reward_user_cart_id AS col0, -- ruc.insert_date, --ruc.send_date, Cast(Cast(ruc.insert_date AS DATETIME2) AS VARCHAR) AS col1, Cast(Cast(ruc.send_date AS DATETIME2) AS VARCHAR) AS col2, re_r.company_employee_id AS col3, re_r.first_name AS col4, re_r.last_name AS col5, rue_r.suffix AS col6, re_r.email AS col7, rwd.display_name AS col8, ruc.amount AS col9, rce.TEAM_NAME AS col10, sts.statusList_Name AS col11, CASE WHEN caregiver_institute_approved.reward_user_cart_id IS NULL THEN 'NO' ELSE 'YES' END AS col12, ruc.message AS col13, rce.reason AS col14, CASE WHEN ruc.delivery_method_id = 1 THEN 'EMAIL' ELSE 'PRINT' END AS col15, rce.GENERAL_VALUES AS col16, rce.category AS col17, rue_r.SUPV_LEVEL AS col18, rue_r.manager_no AS col19, re_r_m.first_name + ' ' + re_r_m.last_name AS col20, rue_r.department AS col21, rue_r.institute AS col22, rue_r.job_title AS col23, rue_r.department_no AS col24, rue_r.accounting_unit AS col25, rue_r.ORG AS col26, rue_r.pay_cycle AS col27, rue_r.job_class AS col28, re_s.company_employee_id AS col29, re_s.email AS col30, re_s.first_name AS col31, re_s.last_name AS col32, rue_s.SUPV_LEVEL AS col33, rue_s.manager_no AS col34, re_s_m.first_name + ' ' + re_s_m.last_name AS col35, rue_s.department AS col36, rue_s.job_title AS col37, rue_s.institute AS col38, rue_s.department_no AS col39, rue_s.accounting_unit AS col40, rue_s.ORG AS col41, rue_s.pay_cycle AS col42, rue_s.job_class AS col43 FROM dbo.reward_user_cart ruc WITH(NOLOCK) INNER JOIN dbo.workstride_reward rwd WITH(NOLOCK) ON rwd.id = ruc.reward_id AND rwd.company_id = 264 INNER JOIN dbo.reward_employees re_r WITH(NOLOCK) ON re_r.user_id = ruc.gc_to_id INNER JOIN dbo.reward_employees re_s WITH(NOLOCK) ON re_s.user_id = ruc.user_id INNER JOIN dbo.statusList sts WITH(NOLOCK) ON sts.statusList_ID = ruc.status_id LEFT JOIN dbo.workstride_hierarchy h_r WITH(NOLOCK) ON ruc.gc_to_id = h_r.user_id LEFT JOIN dbo.workstride_hierarchy h_s WITH(NOLOCK) ON ruc.user_id = h_s.user_id LEFT JOIN dbo.ws_user_flat_t_264 rue_r WITH(NOLOCK) ON rue_r.id = ruc.gc_to_id LEFT JOIN dbo.ws_user_flat_t_264 rue_s WITH(NOLOCK) ON rue_s.id = ruc.user_id LEFT JOIN dbo.ws_recognition_flat_t_264 rce WITH(NOLOCK) ON rce.reward_user_cart_id = ruc.reward_user_cart_id LEFT JOIN dbo.gc gc WITH(NOLOCK) ON gc.gcid = ruc.gcid LEFT JOIN dbo.reward_employees re_r_m WITH(NOLOCK) ON re_r_m.user_id = h_r.parent_user_id AND re_r_m.company_id = 264 LEFT JOIN dbo.reward_employees re_s_m WITH(NOLOCK) ON re_s_m.user_id = h_s.parent_user_id AND re_s_m.company_id = 264 LEFT JOIN (SELECT ruc.reward_user_cart_id FROM dbo.reward_user_cart ruc INNER JOIN dbo.workstride_reward rwd ON rwd.id = ruc.reward_id INNER JOIN dbo.workstride_reward_approval ra ON ra.reward_user_cart_id = ruc.reward_user_cart_id WHERE rwd.company_id = 264 AND rwd.NAME IN ( 'caregiver_award', 'excellence_award' ) AND ra.status_id = 3 AND ( rwd.NAME != 'excellence_award' OR ra.approval_level = 2 ) AND ( rwd.NAME != 'caregiver_award' OR ra.approval_level IN ( 2, 3 ) )) caregiver_institute_approved ON ruc.reward_user_cart_id = caregiver_institute_approved.reward_user_cart_id WHERE rwd.company_id = 264 AND COALESCE(gc.deleted, 0) = 0 AND ruc.is_deleted = 0 and DATEDIFF(d, ruc.send_date, GETDATE()) < 365 GROUP BY ruc.reward_user_cart_id, -- ruc.insert_date, --ruc.send_date, Cast(Cast(ruc.insert_date AS DATETIME2) AS VARCHAR), Cast(Cast(ruc.send_date AS DATETIME2) AS VARCHAR), re_r.company_employee_id, re_r.first_name, re_r.last_name, rue_r.suffix, re_r.email, rwd.display_name, ruc.amount, rce.TEAM_NAME, sts.statusList_Name, CASE WHEN caregiver_institute_approved.reward_user_cart_id IS NULL THEN 'NO' ELSE 'YES' END, ruc.message, rce.reason, CASE WHEN ruc.delivery_method_id = 1 THEN 'EMAIL' ELSE 'PRINT' END, rce.GENERAL_VALUES, rce.category, rue_r.SUPV_LEVEL, rue_r.manager_no, re_r_m.first_name + ' ' + re_r_m.last_name, rue_r.department, rue_r.institute, rue_r.job_title, rue_r.department_no, rue_r.accounting_unit, rue_r.ORG, rue_r.pay_cycle, rue_r.job_class, re_s.company_employee_id, re_s.email, re_s.first_name, re_s.last_name, rue_s.SUPV_LEVEL, rue_s.manager_no, re_s_m.first_name + ' ' + re_s_m.last_name, rue_s.department, rue_s.job_title, rue_s.institute, rue_s.department_no, rue_s.accounting_unit, rue_s.ORG, rue_s.pay_cycle, rue_s.job_class ---order by ruc.insert_date desc ---ORDER BY Cast(Cast(ruc.send_date AS DATETIME2) AS VARCHAR) DESC ---ORDER BY ruc.send_date DESC order by 1 desc
sql-server-2008
queryopti.sql (7.5 KiB)
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
You need to look at the execution plan to see where the time and resources are being spent. As you've already experienced, sorting data can be slow - and I suspect that given the complexity of the query, it has to produce all the rows to then work out the top 200 - so don't think that you are limiting the query by using TOP - it's just an additional filter that's done at the end of processing.
2 Likes 2 ·
anthony.green avatar image anthony.green commented ·
remove all the NOLOCK, its not a magical go faster switch, it produces dirty data and can even be slower than without it. but without the execution plan cant even start to diagnose places where it is slow.
0 Likes 0 ·
sindhu avatar image sindhu commented ·
yes, I need all rows to show not just top 200.I am not able to attach the execution plan it's more than allowed KB.Is there a way I can drop the execution plan?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
0 Likes 0 ·
sindhu avatar image sindhu commented ·
Yeah, I took out the top and ran by replacing with this order by ruc.reward_user_cart_id desc. Attached the new plan
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
The sort is there for the grouping (aggregation) - are you doing that just to get rid of duplicates? If so can you eliminate them earlier?
0 Likes 0 ·

1 Answer

·
sindhu avatar image
sindhu answered
I removed the top and replaced with this orderby order by ruc.reward_user_cart_id desc and attached the new plan

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.