bring down logical reads

Question

I am using stackoverflow2013 database and my query is

select p.CreationDate, p.title,
c.Text,
c.Score
from comments c join posts p on c.PostId = p.id
where p.CreationDate between ‘2008-08-29 00:00:00’ and ‘2008-09-02 23:59:58’ order by c.score desc

and before creating the index I got below - execution time is 18 minutes and 26 seconds

    (2055 rows affected)

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Comments’. Scan count 1, logical reads 1028410, physical reads 4, read-ahead reads 1027512, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Posts’. Scan count 1, logical reads 4184557, physical reads 0, read-ahead reads 4178310, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

then I went and created following indices

create index forcomments on dbo.comments(postid asc ,  Score desc) include (text)
create index forposts on dbo.posts(creationdate asc) include (title)

after creating this, the performance of the query is phenomenal. total execution time is now 0 seconds.

(2055 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Comments'. Scan count 4536, logical reads 19562, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I want to ask how can we bring the logical reads even lower than we already have?

Few points - Statistics is up to date and no incorrect cardinality was observed in execution plan analysis.

Answer 1

I want to ask how can we bring the logical reads even lower than we already have?

The only way would be an indexed view to materialize the join.