Strange Performance Query

Last Post 15 Oct 2007 02:53 AM by tom27. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
johnhillhouse
New Member
New Member

--
02 Jul 2007 08:03 AM
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,
CASE @intInputParam3
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))
ELSE RTrim(dbo.text_de(list.title))
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
AND (
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.
nosepicker
New Member
New Member

--
02 Jul 2007 12:18 PM
Try updating the statistics for the tables referenced in the query.
SQLUSA
New Member
New Member

--
04 Oct 2007 02:34 AM
Reindex your tables and recompile the sprocs.
SwePeso
New Member
New Member

--
07 Oct 2007 09:14 AM
What do this function do?
dbo.text_de(list.title)
SQLUSA
New Member
New Member

--
08 Oct 2007 08:11 PM
AND (
dbo.is_system_admin(@personID) = 1 OR --condition 1
list.group_id IS NULL OR --condition 2
@intInputParam1 = user_group.person_id --condition 3
)


Your condition 1 is a function call. That can kill a query pretty fast.

Kalman Toth, Database Architect
SQL Server 2005 Training - htttp://www.sqlusa.com
tom27
New Member
New Member

--
15 Oct 2007 02:53 AM
post the execution plan, so that we can have a better idea
You are not authorized to post a reply.

Acceptable Use Policy