如何加速此 Row_Number 查询? [英] How do I speed up this Row_Number query?

问题描述

这是我的查询:

SELECT * 
FROM 
    (SELECT 
        ROW_NUMBER() OVER (ORDER BY NAME asc) peta_rn, 
        peta_query.* 
     FROM 
         (SELECT 
              BOOK, PAGETRIMMED, NAME, TYPE, PDF 
         FROM 
              CCWiseDocumentNames2 cdn
         INNER JOIN 
              CCWiseInstr2 cwi ON cwi.ID = cdn.ID) as peta_query) peta_paged 
WHERE 
    peta_rn > 1331900 AND peta_rn <= 1331950

目前这个查询需要大约 4 秒才能得到结果.有什么办法可以让它在 1 秒以内?

Currently this query takes about 4 seconds to get the results. Is there any way to bring it under 1 second?

索引已经在 cwi.ID 和 cdn.ID 上创建.以下是来自 sql server 的实际执行计划:

Index is already created on cwi.ID and cdn.ID. Below is the actual execution plan from sql server:

任何帮助都会有用.

这是表结构:

    /****** Object:  Table [dbo].[CCWiseInstr2]    Script Date: 9/17/2013 3:54:27 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[CCWiseInstr2](
        [ID] [int] NULL,
        [BK_PG] [varchar](50) NULL,
        [DATE] [datetime] NULL,
        [ITYPE] [varchar](50) NULL,
        [BOOK] [int] NULL,
        [PAGE] [varchar](50) NULL,
        [NOBP] [varchar](50) NULL,
        [DESC] [varchar](240) NULL,
        [TIF] [varchar](50) NULL,
        [INDEXNAME] [varchar](50) NULL,
        [CONFIRM] [varchar](50) NULL,
        [PDF] [varchar](50) NULL,
        [PAGETRIMMED] [varchar](10) NULL
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    /****** Object:  Index [IX_CCWiseInstr2_ID]    Script Date: 9/17/2013 3:54:27 AM ******/
    CREATE NONCLUSTERED INDEX [IX_CCWiseInstr2_ID] ON [dbo].[CCWiseInstr2]
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO



/****** Object:  Table [dbo].[CCWiseDocumentNames2]    Script Date: 9/17/2013 3:54:18 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CCWiseDocumentNames2](
    [ID] [int] NULL,
    [BK_PG] [varchar](50) NULL,
    [NAME] [varchar](100) NULL,
    [OTHERNAM] [varchar](100) NULL,
    [TYPE] [varchar](50) NULL,
    [INDEXNAME] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Index [IX_CCWiseDocumentNames2_ID]    Script Date: 9/17/2013 3:54:18 AM ******/
CREATE NONCLUSTERED INDEX [IX_CCWiseDocumentNames2_ID] ON [dbo].[CCWiseDocumentNames2]
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_CCWiseDocumentNames2_NAME]    Script Date: 9/17/2013 3:54:18 AM ******/
CREATE NONCLUSTERED INDEX [IX_CCWiseDocumentNames2_NAME] ON [dbo].[CCWiseDocumentNames2]
(
    [NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

推荐答案

你不需要有 PK 或 Identity，所以你仍然可以在 ID 列上创建聚集索引.它允许重复值，如果您没有附加 ID，而是在中间添加 ID，那么您唯一应该担心的是 INSERT 性能.

You don't need to have PK or Identity, so you still can create clustered index on ID column. It alows repeating values, and the only thing you should be worrying about is INSERT performance if you're the IDs are not appended, but added in the middle.

为什么在外部查询中使用 row_number()?我认为可以在单选中实现相同的结果(也许您必须更改排名功能并使用分区).顺便说一句，如果您的内部查询不返回唯一的 NAME 并且您使用 row_number 而不分区，那么 peta_rn 可能会返回误导性值(具有许多不同 peta_rn 的相同名称).我只是猜测，因为我不知道你到底想达到什么目的.

Why are you using row_number() in outer query? I think the same result can be achieved in single select (maybe you'll have to change the ranking function and use partitioning). Btw if your inner query does not return unique NAME and you're using row_number without partitioning, then peta_rn may return misleading values (same name with many different peta_rn). I'm just guessing because I don't know what exactly are you trying to achieve.

使用聚集索引，您将在 1 秒内完成它，没问题.

Go with the clustered index and you'll bring it under 1s no problem.

这篇关于如何加速此 Row_Number 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！