3

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

Creation

--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

Query

--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.

  • You don't know how efficient caching is? Pretty easy. Say the request is made 1000 / minute and you only need accuracy to the minute. You saved 999 queries and got 1000:1 efficiency. – paparazzo Feb 17 '16 at 19:37
  • 1
    How many rows are we talking about? Have you done any load testing to see if this will be an issue? Also, why are you performing a SUM and not a COUNT on the comment_id? Is its value always 1? – paulbarbin Feb 17 '16 at 19:37
  • 1
    Shouldn't you have a day_id or date range or some other criteria on these queries also? Otherwise, on any normal news day, the most commented posts are all going to be from popular stories days and weeks in the past. The most active users of all time, likewise, won't change often. – Anti-weakpasswords Feb 18 '16 at 8:10
  • @paulbarbin You are right, it should be COUNT and not SUM. – Edu Feb 18 '16 at 15:45
  • @Anti-weakpasswords I gave a very simplified version of the model, to make the question short and to the point. – Edu Feb 18 '16 at 15:50
2

Have you looked into using SQL Server's support for Indexed Views? (Generically this is called a materialized view.) If your code really needs an assist in getting the answers this is a option, though not without its own difficulties.

An Indexed View is a duplicate set of data taken from the main tables and stored within the Indexed View. There are rules and limitations, but it basically creates a specialized "table" that should be index-tuned to suite your purpose for using it.

This MSDN topic points you to the details that you need:

https://msdn.microsoft.com/en-us/library/ms191432.aspx

The definition of an indexed view must be deterministic, needs certain settings to be adjusted, and there are actions such as OUTER JOIN and ORDER BY that are forbidden.

EDIT: You should also use the NOEXPAND hint, as explained by Aaron Bertrand, to "ensure the uniqueness guarantee provided by the view indexes is used by the optimizer." See: http://sqlperformance.com/2015/12/sql-performance/noexpand-hints

  • 1
    And indexed views are updated as part of all DML on the table, just like any other index :). Watch out for performance, though - you'll need to tune and index carefully. – Anti-weakpasswords Feb 18 '16 at 8:05
  • That is precisely what I was looking for. The "Estimated execution plan" points out big improvements when using Indexed Views in SQL Server. – Edu Feb 18 '16 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.