title: Returning a json object in select - causes slow down
body: <p>In the stored procedure, I'm using <code>CTE</code> table to return a data set as <code>JSON</code>. As the amount of data increased, I have noticed that one particular part of the stored procedure is causing the slowdown after running the execution plan.</p>
  
  <p>There're 4 tables and they are linked using <code>ID</code>. For simplicity, I'm going to refer to them as <code>Table1, Table2, Table3 and Table4</code>.</p>
  
  <pre><code>Table1 
  (
      Id (int primary key), 
      Colum1,
      Colum2,
      ---
  )
  
  Table2
  (
      Id (int primary Key)
      Table1Id (foreign key) --To table 1 Id,(non-unique, non-clustered) index
      Colum1,
      Colum2,
      ---
  )
  
  Table3
  (
      Id (int primary key),
      Table2Id (foreign key) -- To table 2 Id, has (non-unique, non-clustered) index
      Table4id (foreign key) -- To table 4 id, has (non-unique, non-clustered) index
      Colum1,
      Colum2,
      ---
  )
  
  Table4
  (
      Id (int primary key),
      Colum1,
      Colum2,
      --
  )
  </code></pre>
  
  <p>Table1 has roughly <code>55000</code> records and Table2 has <code>75000</code> records. I'm passing a <code>JSON</code> filter to the stored procedure to do some filtering and my final output gets return as a <code>JSON</code> object. However, not the filter nor the returning as json is causing slowdown. In my <code>SELECT</code> statement, I'm building an object within using subquery. That's what is causing the issue.</p>
  
  <p>Here's is the query:</p>
  
  <pre><code>(
   SELECT
  
        Colum1,
        Colum2,
        Colum3,
        ...
        --Causes Slowness
        Object1 = (
                         SELECT t2.Id,
                                t2.Table1Id,
                                t2.Colum1,
                                t2.Colum2,
                                ...
                                -- Causes Slowness
                                Object2 = (
                                    SELECT t3.Id,
                                           t3.Table2id,
                                           t3.Table3Id,
                                           t4.Colum1 + ' ' + t4.Colum2 as newcolumn,
                                           ...
                                    FROM Table3 t3
                                    INNER JOIN Table4 t4
                                        ON t3.Table4Id = t4.Id
                                    WHERE t2.Id = t3.Table2Id
                                    ORDER BY CASE WHEN @OrderByAndOrder = 'colum1 asc' THEN u.Colum1 END ASC,
                                             CASE WHEN @OrderByAndOrder = 'colum1 desc' THEN u.Colum1 END DESC
                                    FOR JSON PATH, INCLUDE_NULL_VALUES
                                    )
                                FROM Table2 t2
                                WHERE t2.Table1Id = s.Id
                                  AND
                                --Search by (;) colum1
                                  (
                                          (ISJSON(@Filter) IS NULL) OR
                                          (JSON_VALUE(@Filter, '$.Colum1') = N'') OR
                                          (JSON_VALUE(@Filter, '$.Colum1') IS NULL) OR
                                          ((ISJSON(@Filter) IS NOT NULL) AND
                                              (JSON_VALUE(@Filter, '$.Colum1') &lt;&gt; N'') AND
                                                  EXISTS( SELECT * FROM STRING_SPLIT(JSON_VALUE(@Filter, '$.Filter'), ';')
                                                          WHERE t2.[Colum1] LIKE CONCAT('%', [Value], '%')
                                                          AND [value] &lt;&gt; ''
                                                      )
                                              )
                                      ) )
                                  ORDER BY
                                  CASE WHEN @OrderByAndOrder = 'Colum1 asc' then t2.[Target] END ASC,
                                  CASE WHEN @OrderByAndOrder = 'Colum1 desc' then t2.[Target] END DESC,
                                  FOR JSON PATH, INCLUDE_NULL_VALUES
                            )
  FROM Table1 (NOLOCK) mainTable
  INNER JOIN Table2 t2
   ON mainTable.Id = t2.Table1Id
  WHERE
  mainTable.Id IN (SELECT Id FROM #Ids) --Could potentially join
  AND
  ...
  --same JSON filters on columns in mainTable
  
  --Doing some more stuff with cte
  </code></pre>
  
  <p>Populating <code>Object1</code> and <code>Object2</code> within <code>Object1</code> is causing significant load on this procedure. Is there anything I can do to improve the performance and keep the objects as is?</p>