ROW_NUMBER() Performance optimization

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.

src : http://stackoverflow.com/questions/20230172/row-number-performance-optimization