問題描述

這是我的查詢:

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 秒才能得到結(jié)果.有什么辦法可以讓它在 1 秒以內(nèi)?

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

索引已經(jīng)在 cwi.ID 和 cdn.ID 上創(chuàng)建.以下是來自 sql server 的實際執(zhí)行計劃:

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

任何幫助都會有用.

這是表結(jié)構(gòu):

    /****** 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 列上創(chuàng)建聚集索引.它允許重復(fù)值，如果您沒有附加 ID，而是在中間添加 ID，那么您唯一應(yīng)該擔(dān)心的是 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()?我認為可以在單選中實現(xiàn)相同的結(jié)果(也許您必須更改排名功能并使用分區(qū)).順便說一句，如果您的內(nèi)部查詢不返回唯一的 NAME 并且您使用 row_number 而不分區(qū)，那么 peta_rn 可能會返回誤導(dǎo)性值(具有許多不同 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 秒內(nèi)完成它，沒問題.

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

這篇關(guān)于如何加速此 Row_Number 查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！