SQL ORDER BY ID Desc/asc add a sorted field to resolve query slow problem _mssql

The workaround is to add a sorted field to the order by ID Desc, which can improve the speed a lot. The sorted fields vary by query
such as table

Copy Code code as follows:

CREATE TABLE [dbo]. [Cmpp_sendcentre] (
[ID] [int] IDENTITY (1, 1) not NULL,
[Sendtype] [varchar] (a) COLLATE chinese_prc_ci_as not NULL,
[Senddate] [DateTime] Not NULL,
[Port] [varchar] (m) COLLATE chinese_prc_ci_as not NULL,
[SERVICE_ID] [varchar] (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] (COLLATE) chinese_prc_ci_as not NULL,
[Stat] [varchar] (a) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Go
The relationship of Cmpp_sendcentremo.sendcentreid and cmpp_sendcentre.id into outer building

So I built a view.

Copy Code code 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 ' then ' free ' when ' then ' on-demand ' else ' ' monthly ' 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 is

Copy Code code as follows:

Select top 6*from [Viewcmpp_sendcentremo]
where Sendtype = ' deduction fee '
ORDER BY id DESC

I found it very slow.
After understanding, the reason is that the order by ID DESC/ASC query is a line of search data, so very slow
and changed it.

Copy Code code as follows:

Select top 6*from [Viewcmpp_sendcentremo]
where Sendtype = ' deduction fee '
ORDER BY Sendcentreid Desc, id desc

The query is very fast.