Shulou(Shulou.com)05/31 Report--

This article shows you SQL order by ID desc/asc plus a sorted field to solve the problem of slow query example analysis, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

The solution is to add a sorting field to the order by ID desc, which may make it much faster. The sorted fields vary according to the query as shown in the table

The 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

So I built a view.

The 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 'VOD' else 'monthly Plan' 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 initial query statement is

The code is as follows: select top 6*from [ViewCMPP_SendCentreMo] where SendType = 'deduction' order by id desc

It is found that it is very slow to understand, because the order by id desc/asc query is looking for data line by line, so it is very slow, so it is changed to

The code is as follows: select top 6*from [ViewCMPP_SendCentreMo] where SendType = 'deduction' order by SendCentreID desc, id desc

The query is very fast.

The above is an example analysis of SQL order by ID desc/asc adding a sorted field to solve the problem of slow query. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.