title: Why is SQL Server table-valued function insert taking very long?
body: <p>I have a pretty complex query, but I applied some indexing and now it runs very smoothly in less than 1 second. The structure of the query is like this (I find it unnecessary to post the full query as I will later prove - the fault is not in the query itself):</p>
  
  <pre><code>DECLARE @period varchar(6);
  SET @period = '201302';
  
  DECLARE @day datetime;
  SET @day = dba.fnu_firstdate(@period);//returns 2013-02-01
  
  SELECT
      user_id,
      (SELECT CAST(MAX(c1) AS varchar) FROM table t WHERE t.user_id = table.user_id AND when = DATEADD(day, 0, @day)) Day01,
      ...    
      (SELECT CAST(MAX(c1) AS varchar) FROM table t WHERE t.user_id = table.user_id AND when = DATEADD(day, 30, @day)) Day31
  FROM
      table
  </code></pre>
  
  <p>So yeah, if I execute this query, it takes about 1 second to complete, which is perfectly fine for me. However, as you can see, I need to supply parameter for it. Thus I changed it to a table valued function so I could easily make select queries from it:</p>
  
  <pre><code>CREATE FUNCTION fnu_data(@period varchar(6))
  RETURNS @results TABLE
  (
      id int,
      Day01 varchar(10) null,
      ...
      Day31 varchar(10) null
  )
  AS
  BEGIN
      DECLARE @day datetime;
      SET @day = dba.fnu_firstdate(@period);
  
      INSERT INTO @results 
      (
          id,     
          Day01,
          ...
          Day31
      )
      SELECT
  
  SELECT
      user_id,
      (SELECT CAST(MAX(c1) AS varchar) FROM table t WHERE t.user_id = table.user_id AND when = DATEADD(day, 0, @day)) Day01,
      ...    
      (SELECT CAST(MAX(c1) AS varchar) FROM table t WHERE t.user_id = table.user_id AND when = DATEADD(day, 30, @day)) Day31
  FROM
      table
  
  RETURN
  </code></pre>
  
  <p>Now when I do </p>
  
  <pre><code>SELECT * FROM dba.fnu_data('201302')
  </code></pre>
  
  <p>it takes 6 seconds, which is way too long. Advised by my colleague I tried adding a primary index on id and replacing every subselect as joins, but it extended the time to execute the query to 8 seconds. (P.S. the query returns ~3200 rows).</p>
  
  <p>In my opinion, the culprit is the insertion, but I do not see how I can get rid of it.</p>
  
  <p>What can I do to improve my query?</p>