Pre-filtering table to speed up query in MS SQL

In one of my tasks I had to edit enormous query that is responsible for displaying complicated table on one of our views. As a part of this task I had to join table and take the first element of it to perform further calculation. The solution looks somehting like this:

# lots of sql here...

LEFT JOIN (
  SELECT
    id,
    post_id,
    ROW_NUMBER() OVER (
      PARTITION BY post_id
      ORDER BY id DESC
    ) row_num
  FROM
    comments
  WHERE
    deleted_at IS NULL
) c ON c.post_id = author_stats.post_id AND row_num = 1
LEFT JOIN ...

# lots of sql here...

Unfortunately, this happen to be quite slow because of the enormous number of data we were working on.

So by changing the offending query to specifically pre-filtering the posts table I was able to get the query from 15 seconds to 3 (in the biggest account -> which was acceptable):

DECLARE @comments_sub TABLE (id INT, post_id INT )
 
INSERT INTO @comments_sub (id, post_id)
  SELECT c.id, c.bond_id
  FROM (
    SELECT
      id,
      lcc.post_id,
      ROW_NUMBER() OVER (
        PARTITION BY c.bond_id
        ORDER BY id DESC
    ) row_num  
  FROM comments c
  INNER JOIN listed_comments lc ON c.post_id = lc.post_id
  WHERE deleted_at IS NULL) c
  WHERE c.row_num = 1

and then in query instead of the first snipped we can just use:

# lots of sql here...

LEFT JOIN @comments_sub c on c.post_id = author_stats.post_id
LEFT JOIN ...

# lots of sql here...

Tweet