0

I'm getting crazy trying to make this function fast. I have an employee hierarchy relation between employee and manager. Here is my function

CREATE FUNCTION [dbo].[EmployeeHierarchy] (
    @PeriodIds AS [pit].[PeriodIds] READONLY, 
    @EmployeeId int
)
RETURNS TABLE 
AS 
RETURN 
(   

    WITH relation AS 
    (
       SELECT @EmployeeId as EmployeeId, (SELECT MIN(PeriodId) FROM @PeriodIds) as PeriodId, 0 AS LEVEL

       UNION ALL

       SELECT r.EmployeeId, r.PeriodId, LEVEL + 1 AS LEVEL
         FROM (SELECT EmployeeId, ManagerId, PeriodId FROM dbo.EmployeeManagerRelation) r
        INNER JOIN relation T 
           ON r.ManagerId = T.EmployeeId
        INNER JOIN @PeriodIds P 
           ON p.PeriodId = r.PeriodId
        WHERE r.ManagerId <> r.EmployeeId 
          AND r.PeriodId = T.PeriodId

    )  

    SELECT DISTINCT  EmployeeId, LEVEL FROM relation 

)

If I run this outside the function it takes 0 seconds but as soon as I call the function, it takes forever. I rebuild indexes, update statistics. I also execute this commands:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC FREEPROCCACHE

But nothing works, any idea what's the issue? If it's parameter sniffing, I don't know what to do because I can't create variables, before it wasn't an inline function and I have same issue, I don't know what to do, it's confuse because so other clients use the same but this is not consistent

This is how I call the function :

DECLARE @PeriodIds AS [pit].[PeriodIds]

    INSERT INTO @PeriodIds
    SELECT [PeriodId]
      FROM dbo.[Period]
     WHERE PeriodMonth = 1
       and PeriodYear = 2015

SELECT EmployeeId FROM [dbo].[EmployeeHierarchy](@PeriodIds, 322)
carlosm
  • 687
  • 2
  • 14
  • 29
  • 1
    Please start by checking the execution plan: http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Eduardo Jan 29 '16 at 19:15
  • if I try to run the estimated execution plan, it doesn't respond. is there any way to reset the statistics or something? – carlosm Jan 29 '16 at 19:19
  • How are you calling the function when it takes so much time? It could be the way you are using it(i.e. nonSARGable predicate). In addition to the execution plan the table structures including indexes would help here. – Sean Lange Jan 29 '16 at 19:19
  • The estimated plan might help and will likely contain come useful information but the actual execution plan would be preferred (not always possible if the query takes an hour or whatever). – Sean Lange Jan 29 '16 at 19:20
  • I added the call to the function – carlosm Jan 29 '16 at 19:27
  • You could certainly try updating statistics, but I doubt that would help if the exact same query works fast when it's not called in a function. Maybe using Recompile would help. This article has a lot to say about parameter sniffing in TVFs: http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options – Tab Alleman Jan 29 '16 at 19:28
  • Actually I see a lot of promising-looking articles in these results: https://www.google.com/search?sourceid=navclient&aq=&oq=sql+server+table+valued+function+with+recompile&ie=UTF-8&rlz=1T4GGNI_enUS551US551&q=sql+server+table+valued+function+with+recompile&gs_l=hp....0.0.6.9818614...........0.jkp2fv6aXDU – Tab Alleman Jan 29 '16 at 19:30
  • I can't use recompile inside CTE I guess – carlosm Jan 29 '16 at 19:44
  • what it's funny about this is that for some clients the same function works very fast and for some others no – carlosm Jan 29 '16 at 19:57
  • Hopefully the Query Optimizer is smart enough to rewrite this part, but if not, change this ( `FROM (SELECT EmployeeId, ManagerId, PeriodId FROM dbo.EmployeeManagerRelation)` ) to simply be ( `FROM dbo.EmployeeManagerRelation` ). Also, Inline TVFs do not maintain their own plan since their definition is inserted into the calling query, hence no use in sticking a recompile in there. Also, why call FREEPROCCACHE twice? – Solomon Rutzky Jan 29 '16 at 20:00
  • I was just trying many things :) – carlosm Jan 29 '16 at 20:07
  • Do you have to stick with the adjacency list model? If you converted this to nested sets you would be able to eliminate the recursion and quite possibly the entire need for a function. Here is a great article that discusses this type of conversion. http://www.sqlservercentral.com/articles/Hierarchy/94040/ – Sean Lange Jan 29 '16 at 21:17
  • I can't change my code at this moment – carlosm Jan 29 '16 at 21:35
  • Looks like parameter sniffing to me. Add `OPTION(RECOMPILE)` at the very end of your query, see what that gives. – TT. Jan 29 '16 at 21:51
  • Also this derived table is totally unnecessary: `(SELECT EmployeeId, ManagerId, PeriodId FROM dbo.EmployeeManagerRelation) r`... just start with `dbo.EmployeeManagerRelation` in your FROM clause, lose the derived table. – TT. Jan 29 '16 at 21:53
  • I can't put OPTION(RECOMPILE) inside CTE – carlosm Jan 29 '16 at 22:07
  • I mean I can use it but because is an inline function I cannot – carlosm Jan 29 '16 at 22:17
  • Let me quote myself: *"Add `OPTION(RECOMPILE)` at the very end of your query"*. Not in your CTE, at the end of the whole query. This `OPTION` is for the whole query, CTE included. An additional option to try is `OPTION(OPTIMIZE FOR UNKNOWN)` to prevent parameter sniffing. – TT. Jan 30 '16 at 12:47
  • Also if you want to address someone in comments and want to have them alerted, put the name of the addressee with a `@` in front. Eg `@TT.` if you want to address me. – TT. Jan 30 '16 at 12:48
  • Ok @TT, let me try it – carlosm Jan 30 '16 at 15:54
  • @TT, I can't add this hint inside my inline function, do you have an example of how? – carlosm Jan 30 '16 at 16:08

0 Answers0