I have doubts on database design.
I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example.
I have a news website with news posts, and those have comments. The Comments table is like this:
Table Comments: comment_id | text | post_id | user_id | ...
This news website has in its mainpage two rankings, making them heavily read, but with a low number of writes:
- One with the most commented news posts (The 10 most "popular" news posts)
- Another with the users with most comments (The 10 most "active" users)
The "normal" way to do this is to create the appropriate queries and leave them on that index page:
Top10 of Posts: SELECT TOP 10 post_id, COUNT(comment_id) AS num_comments FROM Comments GROUP BY post_id ORDER BY num_comments DESC
Which could be heavy to process, since it has to process ALL the news comments rows every time the page is accessed. Well, I'm not sure about this because I don't know how efficient caching is.
That's why I'm thinking in a alternative, that would be to create two tables, that would have the processed data for those two rankings.
Table Comments_PostsTop: post_id | num_comments Table Comments_UsersTop: user_id | num_comments
Would this be a good alternative?
If so, is there an automated way to update these tables based on updates on the Comments table besides triggers? Or are triggers the only good way to achieve this?
Or is caching enough to keep high performance? (SQL Server 2014)
Or is there another model that could be more efficient?
EDIT: After accepting the answer, I'm posting how I created and queried the materialized view. It's based on the steps provided by the documentation: https://msdn.microsoft.com/en-us/library/ms191432.aspx
--Create view with schemabinding. IF OBJECT_ID ('dbo.commentsposts', 'view') IS NOT NULL DROP VIEW dbo.commentsposts; GO CREATE VIEW dbo.commentsposts WITH SCHEMABINDING AS SELECT post_id, COUNT_BIG(*) AS num_comments FROM dbo.comments GROUP BY post_id; GO --Create an index on the view. CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON dbo.commentsposts (post_id); GO
--Normal query SELECT post_id, COUNT_BIG(*) AS num_comments FROM comments GROUP BY post_id; --Query for the view SELECT [post_id],[num_comments] FROM [teste].[dbo].[commentsposts]; --But SQL Server is not using the view, so we need to force its usage SELECT [post_id],[num_comments] FROM [teste].[dbo].[commentsposts] WITH (NOEXPAND);
Only on this 3rd query the query selects the data on the view. The first two are the same according to the estimated execution plan.