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 = lctx.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 lc_comments lcc ON c.post_id = lcc.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 = lctx.post_id LEFT JOIN ... # lots of sql here...