title: Sql IN clause slows down performance
body: <p>I need a help about sql query performance... </p>
  
  <p>I have a view and when I run view as </p>
  
  <pre><code>select * 
  from udv_salesAnalyze 
  where _month=12 and _year=2012 
  </code></pre>
  
  <p>I got result in 2 seconds </p>
  
  <p>but when I add another filter as </p>
  
  <pre><code>select * from udv_salesAnalyze 
  where _month=12 and _year=2012 
  and userRef in (1,2,5,6,9,11,12,13,14
  ,19,22,25,26,27,31,34,35,37,38,39,41,47,48,49,53,54,57,59,61,62
  ,65,66,67,68,69,70,74,77,78,79,80,83,86,87,88,90,91,92,94)  
  </code></pre>
  
  <p>I got result in 1 min 38 seconds..</p>
  
  <p>I modified query as </p>
  
  <pre><code>select * from udv_salesAnalyze 
  where _month=12 and _year=2012 
  and userRef in (select * from udf_dependedUsers(2)) 
  </code></pre>
  
  <p>(here udf_dependedUsers is table returned Function) I got result in 38 seconds </p>
  
  <p>I joined table retuned function to view but again I got result in 38-40 seconds...</p>
  
  <p>is there any other way to get result more fastly...</p>
  
  <p>I ll be very appreciated you can give me a solution...</p>
  
  <p>thanks a lot ... </p>
  
  <p><img src="https://i.stack.imgur.com/giyWw.png" alt="execution plan :"></p>
  
  <p>here code fo udf_dependedUsers :</p>
  
  <pre><code>ALTER FUNCTION [dbo].[udfn_dependedUsers] (@userId int)
  RETURNS @dependedUsers table (userRef int)
  AS
  BEGIN
  DECLARE @ID INT
  SET @ID = @userId
  ;WITH ret AS(SELECT userId FROM users
               WHERE  userId = @ID
               UNION ALL
               SELECT t.userId
               FROM   users t INNER JOIN ret r ON t.Manager = r.userId
               ) 
  insert into @dependedUsers (userRef)
  select * from ret
  order by userId
  RETURN 
  END
  </code></pre>