I have a stored procedure that is giving me a bit of grief. I've narrowed it down to the following select query in the sp...
SELECT DISTINCT list.rct_id AS id,
WHEN 1 THEN RTrim(coalesce(list.level_id, -2))
WHEN 2 THEN RTrim(coalesce(list.group_id, -2))
WHEN 3 THEN RTrim(coalesce(rct_ver.values_enabled, -2))
END AS description
FROM rct As list
INNER JOIN rct_version rct_ver
ON list.rct_id = rct_ver.rct_id
LEFT JOIN user_group
ON list.group_id = user_group.group_id
LEFT JOIN person
ON person.level_id <= list.level_id
OR list.level_id IS NULL
WHERE (list.deleted <> 1 OR @intInputParam2 = 1)
AND rct_ver.rct_version_no = (SELECT MAX(rct_version_no) FROM rct_version WHERE rct_id = rct_ver.rct_id)
AND person.person_id = @intInputParam1
dbo.is_system_admin(@personID) = 1 OR --condition 1
list.group_id IS NULL OR --condition 2
@intInputParam1 = user_group.person_id --condition 3
ORDER BY description
FOR XML AUTO
... the problem is that the stored proc is timing out. The select query above seems to be the culprit, it takes almost a minute to run. I've narrowed it down a bit further to the 3 conditions that I have commented "condition 1", "condition 2" and "condition 3" above. The strange bit is that every combination of the conditions (i.e. 1 on it's own, 2 on it's own, 1 and 2 only and so on...) run perfectly fine (about 1 second) but when I combine 1 and 3 or 1,2 and 3 it runs slowly. Conditions 1 and 3 aren't referencing the same tables, 1 is using a function that selects from the person table and 3 is referencing the user_group table. Although the user_group and person tables are related through "person_id". It is also probably worth mentioning that this is only happening to one particular instance of this database, all other instances are fine.
Any help would be greatly appreciated.