CODEWITHSUNDEEP
sqlsql-server
McHeimech
McHeimech

Reputation: 123

ROW_NUMBER() Performance optimization

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

Upvotes: 8

Views: 33097

Answers (3)

GarethD
GarethD

Reputation: 69769

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.

Upvotes: 7

ARA
ARA

Reputation: 1316

As you say "Same query with "Over(Order by Article_tbl.ArticleID asc)" is taking 40ms", no doubt that you have an index missing. You should study the query plan (include actual execution plan button in SSMS) One index with all fields covering your OVER(ORDER BY..) may give you good results. ArticleId is implictly here because it is your cluster, respect the order ASC/DESC of your OVER clause.

try:

CREATE INDEX xxx on Article_tcl(LastEditDate desc, ArticleOrder asc, LastEditDateTime desc)

asc is the default you don't need to specify, here for clarity

Upvotes: 1

Ryan Nigro
Ryan Nigro

Reputation: 4619

I'd break the query up in to component parts. Specifically, you don't need to bring back all column data in your subquery which determines row number and primary keys. Additionally, breaking up the query will make it much easier to debug and optimize.

CREATE TABLE #OrderedRows( RowNumber int primary key, ArticleID int);
Create nonclustered index IX_OrderedRows_ByArticled on #OrderedRows (ArticleID);

insert into #OrderedRows
Select ROW_NUMBER() Over(Order by LastEditDate desc, ArticleOrder Asc, LastEditDateTime desc) as RowNumber, Article_tbl.ArticleID
From Article_tbl 
INNER JOIN ArticleZone_tbl
    ON dbo.Article_tbl.ArticleID = dbo.ArticleZone_tbl.ArticleID
Where ChannelID=1 And ZoneID=0

SELECT oo.RowNumber, at.*, azt.ChannelID, azt.ZoneID, azt.ArticleOrder
FROM #OrderedOrders oo
join Article_tbl at
  on oo.ArticleID = at.ArticleID
INNER JOIN ArticleZone_tbl azt
    ON at.ArticleID = azt.ArticleID
    and azt.ChannelID = 1
    and azt.ZoneID = 0
Where oo.RowNumber Between 1 And 10

Drop table #OrderedRows;

Also, does adding the following indices affect performance?

create nonclustered index IX_Article_tbl_ByArticle_IncOrder on Article_tbl (ArticleID) Include (ArticleOrder);
create nonclustered index IX_ArticleZone_tbl_Cover on ArticleZone_tbl (ChannelID, ZoneID, ArticleID, ArticleDate, ArticleTime);

Upvotes: 1

Related Questions