如何加快这一ROW_NUMBER查询？(How do I speed up this Row_Num

这是我的查询：

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秒？

指数上已经创建cwi.ID和cdn.ID 。下面是从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

Answer 1:

我认为问题是，这两个表需要之前的where子句可以扔倒垃圾被完全匹配，然后进行排序的名称。

我不知道这会帮助，但它是值得一试 - 尝试添加名称指数：

CREATE NONCLUSTERED INDEX [IX_CCWiseDocumentNames2_ID] ON [dbo].[CCWiseDocumentNames2]
(
    [ID] ASC,
    [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]

Answer 2:

你并不需要有PK或身份，所以你仍然可以创建ID列聚集索引。它alows重复的值，你应该担心的唯一的事情是INSERT的性能，如果你的ID都没有追加，但在中间添加。

你为什么要使用ROW_NUMBER（）在外部查询？我认为同样的结果可以在单一选择来实现（也许你会不得不改变分级功能和使用分区）。顺便说一句，如果你的内部查询不返回唯一的名称和您正在使用ROW_NUMBER不分区，然后peta_rn可能返回误导性的值（相同的名字与许多不同的peta_rn）。我只是猜测，因为我不知道到底是你想实现什么。

与聚集索引去，你就会把它在1秒没有问题。