SQL Server Performance Forum – Threads Archive

User-Defined Function Slower Than Query

I have a user-defined function that navigates down a hierarchy within a single table and returns a table of the uid’s and names. The result is used in joins to ‘include’ all records beneath the supplied uid. When I execute the code extracted from the udf in Query Analyzer, it takes less than one second. When I call the udf in Query Analyzer, it takes two minutes. I have tried supplying index hints with no success. Any helpful direction appreciated!! Thanks.

---

Have you tried putting both the code and the execute for the udf in QA and looking at the query plan for both. If its different, try recompiling the udf. And flush the proc cache just in case. Chris

---

Chris: I tried viewing the ExPlan for both, but the call to the udf in QA only yielded a single item with no drill down details. I solved the problem only a few minutes ago by studying the query ExPlan and noticed it was doing an "inner hash join" where I was doing a subquery, so I gave that a shot, and it worked like a champ. I suppose that behind the scenes, the udf was not being optimized the same as the query. Here is the before and after code… ——-
BEFORE:
——-
/* insert all child nodes */
WHILE @moreRows > 0
BEGIN
INSERT INTO @HierarchyTable
SELECT cc_uid, cc_parent_uid, cc_nam, cc_accounttype, @level + 1
FROM ccdb.dbo.cc_company
WHERE cc_parent_uid IN
(SELECT uid FROM @HierarchyTable WHERE Depth = @level) SELECT
@level = @level + 1,
@moreRows = @@ROWCOUNT
END ——
AFTER:
——
/* insert all child nodes */
WHILE @moreRows > 0
BEGIN
INSERT INTO @HierarchyTable
SELECT cc_uid, cc_parent_uid, cc_nam, cc_accounttype, @level + 1
FROM ccdb.dbo.cc_company WITH (INDEX(pk_company))
INNER HASH JOIN @HierarchyTable ON (cc_parent_uid = uid AND Depth = @level) SELECT
@level = @level + 1,
@moreRows = @@ROWCOUNT
END Thanks for your help!!! –Steven

---

]]>