sql - SQL 分页比获取所有数据花费的时间更长(x15~x20)是否正常？

Question

我有大约 16k 行的 View ，获取所有数据大约需要 5 秒。

我已经决定在应用程序中实现“加载”，这样 GUI 就不会卡住，用户就可以在 DataGridView 中使用/查看/查看提供的数据。

我注意到，如果我使用 SQL 分页获取所有数据，它需要大约 90 秒(1.5 分钟)，所以会适得其反。

现在我想知道它是否正常，如果正常，为什么会有人使用它？

我尝试了 3 种 SQL 分页方式:

I'm using 160 for testing purposes!

DECLARE @int_percentage AS INT = 1

WHILE @int_percentage <= 100
BEGIN
    SELECT O.*, P.Percentage
    FROM vAppointmentDetailsWithComments O
    LEFT JOIN (SELECT AppointmentID, NTILE(100) OVER(ORDER BY AppointmentID) Percentage
                FROM vAppointmentDetailsWithoutComments) P ON P.AppointmentID = O.AppointmentID
    WHERE P.Percentage = @int_percentage

    SET @int_percentage = @int_percentage + 1
END
---------------------------------------------------------------------------------------------------
DECLARE @int_percentage AS INT = 1, @int_appointmentID AS INT = 0

WHILE @int_percentage <= 100
BEGIN
    SELECT TOP 160 *
    FROM vAppointmentDetailsWithComments
    WHERE AppointmentID > @int_appointmentID

    SET @int_percentage = @int_percentage + 1
    SET @int_appointmentID = @int_appointmentID + 161
END
---------------------------------------------------------------------------------------------------
DECLARE @int_percentage AS INT = 1, @int_currentStartingRowIndex AS INT = 1

WHILE @int_percentage <= 100
BEGIN
    EXEC spGetRows @int_startingRowIndex = @int_currentStartingRowIndex, @int_maxRows = 160

    SET @int_percentage = @int_percentage + 1
    SET @int_currentStartingRowIndex = @int_currentStartingRowIndex + 160
END
---------------------------------------------------------------------------------------------------
SELECT *
FROM vAppointmentDetailsWithComments

程序:

CREATE PROCEDURE [dbo].[spGetRows] 
(
    @int_startingRowIndex INT,
    @int_maxRows INT
)
AS

DECLARE @int_firstID INT

-- Getting 1'st ID
SET ROWCOUNT @int_startingRowIndex
SELECT @int_firstID = AppointmentID FROM vAppointmentDetailsWithoutComments ORDER BY AppointmentID

-- Setting ROWCOUNT to MAX
SET ROWCOUNT @int_maxRows

-- Getting all data >= @int_firstID
SELECT *
FROM vAppointmentDetailsWithComments
WHERE AppointmentID >= @int_firstID

SET ROWCOUNT 0

GO

结果:

表和 View 的创建和填充数据:

FOR XML PATH in "vAppointmentDetailsWithComments" is main performance problem

CREATE TABLE [dbo].[Appointment](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Number] [int] NOT NULL,
 CONSTRAINT [PK_Appointment] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Appointment] ADD  CONSTRAINT [DF_Appointment_Number]  DEFAULT ((0)) FOR [Number]
GO
---------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Comment](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Appointment_ID] [int] NOT NULL,
    [Text] [nvarchar](max) NOT NULL,
    [Time] [datetime] NOT NULL,
 CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Comment]  WITH CHECK ADD  CONSTRAINT [FK_Comment_Appointment] FOREIGN KEY([Appointment_ID])
REFERENCES [dbo].[Appointment] ([ID])
GO

ALTER TABLE [dbo].[Comment] CHECK CONSTRAINT [FK_Comment_Appointment]
GO

ALTER TABLE [dbo].[Comment] ADD  CONSTRAINT [DF_Comment_Text]  DEFAULT (N'Some random Comment for Testing purposes') FOR [Text]
GO

ALTER TABLE [dbo].[Comment] ADD  CONSTRAINT [DF_Comment_Time]  DEFAULT (getdate()) FOR [Time]
GO
---------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[vAppointmentDetailsWithComments]
AS
SELECT A.ID AppointmentID, (K.Comments + CHAR(13) + CHAR(10)) Comment
FROM Appointment A LEFT JOIN
    (SELECT A.ID,
        (SELECT STUFF
            ((SELECT REPLACE(CHAR(13) + CHAR(10) + K.Text, CHAR(7), '')
        FROM Comment K
        WHERE K.Appointment_ID = A.ID
        AND K.Text != ''
        ORDER BY K.Time FOR XML PATH, TYPE ).value('.[1]', 'NVARCHAR(MAX)'), 1, 1, '')) Comments
    FROM Appointment A) K ON K.ID = A.ID

GO
---------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[vAppointmentDetailsWithoutComments]
AS
SELECT A.ID AppointmentID
FROM Appointment A

GO
---------------------------------------------------------------------------------------------------
SET NOCOUNT ON 
BEGIN TRAN 
DECLARE @int_appointmentID AS INT = 1,
         @int_tempComment AS INT
WHILE @int_appointmentID <= 16000 
BEGIN 
    INSERT INTO Appointment VALUES (@int_appointmentID)

    SET @int_tempComment = 1

    WHILE @int_tempComment <= 5
    BEGIN
        INSERT INTO Comment (Appointment_ID) VALUES (@int_appointmentID)

        SET @int_tempComment = @int_tempComment + 1
    END

SET @int_appointmentID = @int_appointmentID + 1 
END 
COMMIT TRAN

GO

执行计划: Fast(FetchAll) Slow(Top)

Answer 1

部分性能问题是因为 Comment 表 Appointment_ID 列上没有索引。通过 Appointment_ID 上的聚集索引并将主键索引更改为非聚集索引，来自 vAppointmentDetailsWithComments 的选择查询所用时间在我的测试盒上从大约 5 秒减少到大约 3.5 秒。下面是创建聚集索引并将主键重新创建为非聚集索引的脚本。

ALTER TABLE dbo.Comment DROP CONSTRAINT FK_Comment_Appointment;

ALTER TABLE Appointment DROP CONSTRAINT PK_Appointment;

ALTER TABLE Appointment ADD CONSTRAINT PK_Appointment
    PRIMARY KEY NONCLUSTERED(ID);

ALTER TABLE dbo.Comment 
    ADD CONSTRAINT FK_Comment_Appointment FOREIGN KEY(Appointment_ID)
    REFERENCES dbo.Appointment (ID);


CREATE CLUSTERED INDEX cdx_Comment_Appointment_ID ON Comment(Appointment_ID);
GO

评论的字符串连接是在 T-SQL 中执行的昂贵操作。我建议您在应用程序端执行此操作，我预计这对于 16K 行将是亚秒级的。这将避免需要通过简单的连接到评论来跳过 SQL 端的箍:

CREATE VIEW dbo.vAppointmentDetailsWithIndividualComments
AS
SELECT A.ID AppointmentID, K.Text, K.Time
FROM dbo.Appointment A 
LEFT JOIN dbo.Comment K
        ON K.Appointment_ID = A.ID
        AND K.Text <> '';
GO

SELECT AppointmentID, Text, Time
FROM dbo.vAppointmentDetailsWithIndividualComments
ORDER BY Time;
GO

关于您列出的分页技术，由于对约会的扫描，第一种技术在结果集中的表现会越来越差。

第二个查询缺少 ORDER BY Appointment_ID。 ORDER BY 需要 TOP 以获得确定性结果。但是，从分页性能的角度来看，此方法确实有其优点，因为它将在约会表上执行索引查找，无论结果集中的位置如何，都能提供一致的性能。

SET ROWCOUNT 已弃用，但最重要的是它将执行与第一个查询类似的操作(越来越差)。