解决方法就是在order by id desc再加一个排序的字段，这样子可能会把速度提高很多。再加止排序的字段因查询而异了
如表
复制代码 代码如下:
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 与cmpp_sendcentre.id成外建关系
于是建了一个视图
复制代码 代码如下:
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 ‘免费’ when ’02’ then ‘点播’ else ‘包月’ 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
一开始的查询语句为
复制代码 代码如下:
select top 6*from [viewcmpp_sendcentremo]
where sendtype = ‘扣费’
order by id desc
发现非常的慢
经过了解,原因是order by id desc/asc的查询是一行一行的找数据,所以非常的慢
于是改成了
复制代码 代码如下:
select top 6*from [viewcmpp_sendcentremo]
where sendtype = ‘扣费’
order by sendcentreid desc, id desc
查询就非常的快了