The solution is to add a sorting Field in order by ID desc, which may increase the speed a lot. The sorting field is different from the query field.
Such as table
Copy codeThe Code is as follows: create table [dbo]. [CMPP_SendCentre] (
[Id] [int] IDENTITY (1, 1) not null,
[SendType] [varchar] (10) COLLATE Chinese_PRC_CI_AS not null,
[SendDate] [datetime] not null,
[Port] [varchar] (50) COLLATE Chinese_PRC_CI_AS not null,
[Service_ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[FeeType] [varchar] (2) COLLATE Chinese_PRC_CI_AS not null,
[FeeCode] [varchar] (6) COLLATE Chinese_PRC_CI_AS not null,
[Msg_Content] [varchar] (1024) COLLATE Chinese_PRC_CI_AS not null,
[SendCount] [int] not null,
[SucceedCount] [int] NOT NULL
) ON [PRIMARY]
GO
Create table [dbo]. [CMPP_SendCentreMo] (
[Id] [int] IDENTITY (1, 1) not null,
[SendCentreID] [int] not null,
[Mo] [varchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[Stat] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CMPP_SendCentreMo.SendCentreID has an external relationship with CMPP_SendCentre.ID.
A view is created.Copy codeThe Code is as follows: create view dbo. ViewCMPP_SendCentreMo
AS
SELECT
Dbo. CMPP_SendCentreMo.id,
Dbo. CMPP_SendCentreMo.SendCentreID,
Dbo. CMPP_SendCentreMo.Mo,
Dbo. CMPP_SendCentreMo.Stat,
Dbo. CMPP_SendCentre.SendType,
Dbo. CMPP_SendCentre.SendDate,
Dbo. CMPP_SendCentre.Port,
Dbo. CMPP_SendCentre.Service_ID,
Case dbo. CMPP_SendCentre.FeeType when '01 'then' free 'when' 02 'then' On-Demand 'else' monthly subscription 'end as FeeType,
Cast (dbo. CMPP_SendCentre.FeeCode as smallint) as FeeCode,
Dbo. CMPP_SendCentre.Msg_Content
FROM dbo. CMPP_SendCentre INNER JOIN
Dbo. CMPP_SendCentreMo ON
Dbo. CMPP_SendCentre.id = dbo. CMPP_SendCentreMo.SendCentreID
The first query statement isCopy codeThe Code is as follows: select top 6 * from [ViewCMPP_SendCentreMo]
Where SendType = 'deduction'
Order by id desc
Found very slow
After understanding, the reason is that order by id desc/asc queries data in one row, so it is very slow
ChangedCopy codeThe Code is as follows: select top 6 * from [ViewCMPP_SendCentreMo]
Where SendType = 'deduction'
Order by SendCentreID desc, id desc
Query is very fast.