title: How to optimize stored procedures?
body: <p>Following is my Stored Proc.</p>
  
  <pre><code>ALTER PROCEDURE [GetHomePageObjectPageWise]
         @PageIndex INT = 1
        ,@PageSize INT = 10
        ,@PageCount INT OUTPUT
        ,@AccountID INT
        ,@Interests Varchar(3000)
  AS
  BEGIN
        SET NOCOUNT ON;
  
  
  
        SELECT StoryID
        , AlbumID
        , StoryTitle
        , CAST(NULL as varchar) AS AlbumName
        , (SELECT URL FROM AlbumPictures WHERE (AlbumID = Stories.AlbumID) AND (AlbumCover = 'True')) AS AlbumCover
        , Votes
        , CAST(NULL as Int) AS PictureId
        , 'stories' AS tableName
        , (SELECT CASE WHEN EXISTS (
              SELECT NestedStories.StoryID FROM NestedStories WHERE (StoryID = Stories.StoryID) AND (AccountID=@AccountID)
          )
          THEN CAST(1 AS BIT)
          ELSE CAST(0 AS BIT) END) AS Flag
        , (SELECT UserName FROM UserAccounts WHERE Stories.AccountID=UserAccounts.AccountID) AS Username
  
  INTO #Results1
  FROM Stories WHERE FREETEXT(Stories.Tags,@Interests) AND AccountID &lt;&gt; @AccountID AND IsActive='True' AND Abused &lt; 10
  </code></pre>
  
  <p>I have 7 more <code>SELECT</code> Statements (not included in the question for brevity) in the Stored Proc similar to <code>SELECT StoryID</code> statement, which i <code>UNION ALL</code> like this</p>
  
  <pre><code>SELECT * INTO #Results9 FROM #Results1
  UNION ALL
  SELECT * FROM #Results2
  UNION ALL
  SELECT * FROM #Results3
  UNION ALL
  SELECT * FROM #Results4
  UNION ALL
  SELECT * FROM #Results5
  UNION ALL
  SELECT * FROM #Results6
  UNION ALL
  SELECT * FROM #Results7
  UNION ALL
  SELECT * FROM #Results8
  
  SELECT ROW_NUMBER() OVER
              (
                    ORDER BY [tableName] DESC
              )AS RowNumber
              , * INTO #Results
              FROM #Results9
  
  
        DECLARE @RecordCount INT
        SELECT @RecordCount = COUNT(*) FROM #Results
  
        SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
  
  
        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
  
        DROP TABLE #Results
        DROP TABLE #Results1
        DROP TABLE #Results2
        DROP TABLE #Results3
        DROP TABLE #Results4 
  END
  </code></pre>
  
  <p>This takes around 6 seconds to return the result. How can i improve this stored proc? I have very little knowledge about stored procedures.</p>