ROW_NUMBER() Performance optimization

Question

First, I would like to mention that I already checked all other asked questions, and none of it is similar to mine, so I don't think it's a duplicate.

I have two table tables, "Article_tbl" with more than 300,000 rows so far and "ArticleZone_tbl" with almost the same rows count.

"Article_tbl" Contains a Identity primary key, "ArticleID". "ArticleZone_tbl" contains a primary key consisting of three columns, "ArticleID", "ChannelID", "ZoneID"; Where "ArticleID" is a foreign key from "Article_tbl"

Non clustered indexes were created on the columns to order by.

SQL Query:

WITH OrderedOrders AS(
Select ROW_NUMBER() Over(Order by LastEditDate desc, ArticleOrder Asc, LastEditDateTime desc) as RowNum, dbo.Article_tbl.*, ArticleZone_tbl.ChannelID, ArticleZone_tbl.ZoneID, ArticleZone_tbl.ArticleOrder
From Article_tbl INNER JOIN ArticleZone_tbl
    ON dbo.Article_tbl.ArticleID = dbo.ArticleZone_tbl.ArticleID
Where ChannelID=1 And ZoneID=0)

SELECT * FROM OrderedOrders Where RowNum Between 1 And 10

The above query is taking about 2 seconds to complete, is there any way to optimize this query?

More info: OS: Windows WebServer 2008R2 SQL Sever: 2008R2 RAM: 32GB HDD: 160GB SSD

Thanks in advance.

Best regards, McHaimech

Answer 1

You could try creating an Indexed View on the two tables:

CREATE VIEW dbo.YourIndexedView
WITH SCHEMABINDING 
AS
    SELECT  az.ArticleID,
            az.ChannnelID,
            az.ZoneID,
            a.LastEditDate,
            a.LastEditDateTime,
            az.ArticleOrder
    FROM    dbo.Article_tbl a
            INNER JOIN dbo.ArticleZone_tbl az
                ON a.ArticleID = az.AtricleID;

GO
CREATE UNIQUE CLUSTERED INDEX UQ_YourIndexView_ArticleID_ChannelID_ZoneID 
    ON dbo.YourIndexedView (ArticleID, ChannelID, ZoneID);

Once you have your clustered index in place you can create a nonclustered index that would assist in the sorting:

CREATE NONCLUSTERED INDEX IX_YourIndexedView_LastEditDate_ArticleOrder_LastEditDateTime
    ON dbo.YourIndexedView (LastEditDate DESC, ArticleOrder ASC, LastEditDateTime DESC);

You can then reference this in your query:

WITH OrderedOrders AS
(   SELECT  RowNum = ROW_NUMBER() OVER(ORDER BY LastEditDate DESC, ArticleOrder ASC, LastEditDateTime DESC),
            ArticleID,
            ChannelID,
            ZoneID,
            LastEditDateTime,
            ArticleOrder
    FROM    dbo.YourIndexedView WITH (NOEXPAND)
    WHERE   ChannelID = 1 
    AND     ZoneID = 0
)
SELECT  *
FROM    OrderedOrders
WHERE   RowNum BETWEEN 1 AND 10;

N.B. I may have missed some columns from your article table, but I couldn't infer them from the question

Furthermore, if your query is always going to have the same zone and channel, you could filter the view, then your clustered index column simply becomes ArticleID:

CREATE VIEW dbo.YourIndexedView
WITH SCHEMABINDING 
AS
    SELECT  az.ArticleID,
            az.ChannnelID,
            az.ZoneID,
            a.LastEditDate,
            a.LastEditDateTime,
            az.ArticleOrder
    FROM    Article_tbl a
            INNER JOIN ArticleZone_tbl az
                ON a.ArticleID = az.AtricleID
    WHERE   az.ChannelID = 1
    AND     Az.ZoneID = 1;

GO
CREATE UNIQUE CLUSTERED INDEX UQ_YourIndexView_ArticleID 
    ON dbo.YourIndexedView (ArticleID);

Which means your indexes will be smaller, and faster to use.