title: ROW_NUMBER() Performance optimization
body: <p>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.</p>
  
  <p>I have two table tables, "Article_tbl" with more than 300,000 rows so far and "ArticleZone_tbl" with almost the same rows count.</p>
  
  <p>"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"</p>
  
  <p>Non clustered indexes were created on the columns to order by.</p>
  
  <p>SQL Query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The above query is taking about 2 seconds to complete, is there any way to optimize this query?</p>
  
  <p>More info:
  OS: Windows WebServer 2008R2
  SQL Sever: 2008R2
  RAM: 32GB
  HDD: 160GB SSD</p>
  
  <p>Thanks in advance.</p>
  
  <p>Best regards,
  McHaimech</p>