question

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