Strange Performance Query
-
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.
Could some sort of data corruption be causing this?
Any help would be greatly appreciated.
-
1) that's not a very efficient query: distinct, 3 joins (one with <=), correlated subquery and a function call in the where clause. Oh, and an ORDER BY AND XML output! :0
2) If it is only 1 db it is happening on, are the data the same in the two? If so, it is probably either an index needing to be (re)built, or a statistics issue (update them all). I would also consider marking the sproc WITH RECOMPILE to ensure appropriate plan is built every time.
3) Can you remove that function that calls out to person table? This can be VERY inefficient - essentially row-by-row processing here.
For future reference, we could use table defs (including indexes/keys), sample data and row counts and query plan information to help you more effectively.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Execution plan would also be useful
--------------------
Colt 45 - the original point and click interface
-
Thanks for the suggestions.
The data in the affected database is different to the unaffected databases. I've tried dropping and recreating the indexes and running update statistics against the related tables but this hasn't worked. I understand that the query is inefficient, but would this have a bearing on the fact that it runs in 2-3 seconds against one db but almost a minute in another (both dbs have approx same data volume)?
Could this possibly be down to data corruption? How would I detect data corruption, could I use DBCC CHECKDB or DBCC CHECKTABLE?
This is probably a daft question but how would I get the Query Execution Plan to you? Could I use SET SHOWPLAN_ALL? When I use this there seems to be a lot of data returned, too much to attempt posting on this site? On the same note, how would I get table defs (including indexes/keys), sample data and row counts to you?
Thanks for your help so far.
-
SET SHOWPLAN_TEXT ON will work.
But if the data is different, why would you expect similar execution times??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply