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