I'm using following query for configurable reports , but it is giving session timed out error as user count is more than 200 only. Have tried nolock but there is no as such effect of that. Following is the query and also the error message. Any suggestion to optimize or get rid of the error.
Query :
SELECT DISTINCT CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS
NAME, campus_value_department_x.data AS 'department' ,
campus_value_occupation_x.data AS 'occupation'
, c.fullname AS 'Course',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Activity Name'
,ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage
,IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass
FROM prefix_course c
,prefix_context ctx
,prefix_role_assignments ra
,prefix_user u
,prefix_grade_grades gg
,prefix_grade_items gi
,prefix_course_categories cc
,prefix_user_info_data ud
,(SELECT * FROM prefix_user_info_data WHERE fieldid=4 AND userid IN (SELECT userid FROM prefix_cohort_members WHERE cohortid=16)) campus_value_department_x
,(SELECT * FROM prefix_user_info_data WHERE fieldid=5 AND userid IN (SELECT userid FROM prefix_cohort_members WHERE cohortid=16)) campus_value_occupation_x
WHERE 1=1
AND c.id = ctx.instanceid
AND ra.contextid = ctx.id
AND u.id = ra.userid
AND gg.userid = u.id
AND gi.id = gg.itemid
AND cc.id = c.category
AND campus_value_department_x.userid=u.id
AND campus_value_occupation_x.userid=u.id
AND gi.courseid = c.id
AND gi.itemname != 'Attendance'
ORDER BY 'Name' ASC
Error :
Timed out while waiting for session lock.
Wait for your current requests to finish and try again later.
Debug info:
Error code: sessionwaiterr