SQL Server Performance

User-Defined Function Slower Than Query

Discussion in 'T-SQL Performance Tuning for Developers' started by sp_collins, Mar 9, 2004.

  1. sp_collins New Member

    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.
  2. ChrisFretwell New Member

    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
  3. sp_collins New Member

    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

Share This Page