下面是两个表的成生代码和视图代码if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CMPP_SendCentreMo_CMPP_SendCentre]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMPP_SendCentreMo] DROP CONSTRAINT FK_CMPP_SendCentreMo_CMPP_SendCentre
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP_SendCentre]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP_SendCentre]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP_SendCentreMo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP_SendCentreMo]
GOCREATE 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]
GOCREATE 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]
GOALTER TABLE [dbo].[CMPP_SendCentre] WITH NOCHECK ADD
CONSTRAINT [PK_SendCentre] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentreMo] WITH NOCHECK ADD
CONSTRAINT [PK_SendCentreMo] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentre] ADD
CONSTRAINT [DF_SendCentre_SendType] DEFAULT ('普通') FOR [SendType],
CONSTRAINT [DF_SendCentre_SendDate] DEFAULT (getdate()) FOR [SendDate],
CONSTRAINT [DF_SendCentre_SendCount] DEFAULT (0) FOR [SendCount],
CONSTRAINT [DF_SendCentre_SucceedCount] DEFAULT (0) FOR [SucceedCount]
GO CREATE INDEX [IX_SendCentre] ON [dbo].[CMPP_SendCentre]([Port]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_Service_ID] ON [dbo].[CMPP_SendCentre]([Service_ID]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_SendDate] ON [dbo].[CMPP_SendCentre]([SendDate]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_SendType] ON [dbo].[CMPP_SendCentre]([SendType]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo] ON [dbo].[CMPP_SendCentreMo]([SendCentreID]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo_1] ON [dbo].[CMPP_SendCentreMo]([Mo]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo_2] ON [dbo].[CMPP_SendCentreMo]([Stat]) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentreMo] ADD
CONSTRAINT [FK_CMPP_SendCentreMo_CMPP_SendCentre] FOREIGN KEY
(
[SendCentreID]
) REFERENCES [dbo].[CMPP_SendCentre] (
[id]
)
GOCREATE VIEW dbo.ViewCMPP_SendCentreMo
AS
SELECT
SCM.[id],
SCM.[SendCentreID],
SCM.[Mo],
case isnull(SCM.Stat,2) when 'DELIVRD' then '成功' when 2 then '未知' else '失败' end as Stat,SC.[SendType],
SC.[SendDate],
SC.[Port],
SC.[Service_ID],
SC.[FeeType],
SC.[FeeCode],
SC.[SendCount],
SC.[Msg_Content]FROM [CMPP_SendCentreMo] SCM,ViewCMPP_SendCentre SC
where SCM.SendCentreID = SC.ID记录数
SendType 记录数
扣费 393162
普通 2204484当我使用下面的查询语句时非常的慢select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id asc 当改成下面时,就非常的快select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '普通'
order by id asc 如果我再改成下面时,就非常的快select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id desc如果我再改成下面时,就非常的慢select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '普通'
order by id desc
我用的数据库是SQL SERVICE 2000请问各位,这是怎么一回事?表是不是设计的不好了?
ALTER TABLE [dbo].[CMPP_SendCentreMo] DROP CONSTRAINT FK_CMPP_SendCentreMo_CMPP_SendCentre
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP_SendCentre]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP_SendCentre]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CMPP_SendCentreMo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CMPP_SendCentreMo]
GOCREATE 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]
GOCREATE 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]
GOALTER TABLE [dbo].[CMPP_SendCentre] WITH NOCHECK ADD
CONSTRAINT [PK_SendCentre] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentreMo] WITH NOCHECK ADD
CONSTRAINT [PK_SendCentreMo] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentre] ADD
CONSTRAINT [DF_SendCentre_SendType] DEFAULT ('普通') FOR [SendType],
CONSTRAINT [DF_SendCentre_SendDate] DEFAULT (getdate()) FOR [SendDate],
CONSTRAINT [DF_SendCentre_SendCount] DEFAULT (0) FOR [SendCount],
CONSTRAINT [DF_SendCentre_SucceedCount] DEFAULT (0) FOR [SucceedCount]
GO CREATE INDEX [IX_SendCentre] ON [dbo].[CMPP_SendCentre]([Port]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_Service_ID] ON [dbo].[CMPP_SendCentre]([Service_ID]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_SendDate] ON [dbo].[CMPP_SendCentre]([SendDate]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentre_SendType] ON [dbo].[CMPP_SendCentre]([SendType]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo] ON [dbo].[CMPP_SendCentreMo]([SendCentreID]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo_1] ON [dbo].[CMPP_SendCentreMo]([Mo]) ON [PRIMARY]
GO CREATE INDEX [IX_SendCentreMo_2] ON [dbo].[CMPP_SendCentreMo]([Stat]) ON [PRIMARY]
GOALTER TABLE [dbo].[CMPP_SendCentreMo] ADD
CONSTRAINT [FK_CMPP_SendCentreMo_CMPP_SendCentre] FOREIGN KEY
(
[SendCentreID]
) REFERENCES [dbo].[CMPP_SendCentre] (
[id]
)
GOCREATE VIEW dbo.ViewCMPP_SendCentreMo
AS
SELECT
SCM.[id],
SCM.[SendCentreID],
SCM.[Mo],
case isnull(SCM.Stat,2) when 'DELIVRD' then '成功' when 2 then '未知' else '失败' end as Stat,SC.[SendType],
SC.[SendDate],
SC.[Port],
SC.[Service_ID],
SC.[FeeType],
SC.[FeeCode],
SC.[SendCount],
SC.[Msg_Content]FROM [CMPP_SendCentreMo] SCM,ViewCMPP_SendCentre SC
where SCM.SendCentreID = SC.ID记录数
SendType 记录数
扣费 393162
普通 2204484当我使用下面的查询语句时非常的慢select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id asc 当改成下面时,就非常的快select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '普通'
order by id asc 如果我再改成下面时,就非常的快select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id desc如果我再改成下面时,就非常的慢select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '普通'
order by id desc
我用的数据库是SQL SERVICE 2000请问各位,这是怎么一回事?表是不是设计的不好了?
解决方案 »
- 无法将类型“System.Web.UI.WebControls.Label”隐式转换为“string”
- session值取不到
- web.config配置出错,郁闷???
- 请教~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- DataGrid中HyperLink的问题!
- 他提示常量中有换行符。我没找到问题所在
- 怎样取得完整URL后面的内容?(c#)
- datagrid出问题了,郁闷,帮忙哦,路过的兄弟
- 请教:关于DataGrid的绑定问题
- 祸不单行,ASP.NET不能连接到IIS了!
- asp.net WebEditor1 富文本编辑器 使用问题
- GridView隐藏列之后无法取到该列的值
--AreaDigit,BusinessScope,EconomyType 建组合索引
试试
--建索引语句:
--建普通索引
create index 索引名称 on 表名(字段)
--建聚集索引
create clustered index 索引名称 on 表名(字段)
--建非聚集索引
create NONCLUSTERED index 索引名称 on 表名(字段)
CONSTRAINT [PK_SendCentreMo] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
这里已经建立了
请问这个缓存问题要如何解决呢?
CREATE VIEW dbo.ViewCMPP_SendCentre
AS
SELECT id, SendType,SendDate, Port, Service_ID,case FeeType when '01' then '免费' when '02' then '点播' else '包月' end as FeeType, cast(FeeCode as smallint) as FeeCode, Msg_Content,
SendCount,SucceedCount
FROM dbo.CMPP_SendCentre
你可以这样。治标不治本。
放到DATATABLE 在用SELECT(“order by id desc
”)在排一次。呵!
选过了在说.
where SCM.SendCentreID = SC.ID”
这里查询用了两张表我们查询数据的时候,大家都知道如果有索引都会查询会很快的.如果一个字段是索引,则mysql会建一张索引表.就相当一本书的目录.
select a.name from user a where a.id=1.如果id不是索引,那么查义的时间与id是索引有区别的,这里只有一张表看不出来.
select a.name from user a left join user_temp b on a.id=b.id;
这里如果b.id不是索引,查询的速度两个表的大小决定
SendType里面就只有两个值,一个普通,一个是扣费
select top 6 [id],[SendType]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id asc
执行慢 因为top 6要把数据全部走一遍
但是你的扣费多在底部 所以会慢一些
但是普通在上边的多 top6查出来就不再执行了~~
可是如果你从下望上查询时,游标会从顶部先走到n-5行,然后查询那个5组,然后,再回到顶部,再走到n-4行,再查询,你说慢不慢?
这是我的猜想。可能不对哈哈
有些朋友说那个视图有点复杂,我重写了一个.出现的情况还是一样CREATE VIEW dbo.ViewCMPP_SendCentreMo
AS
SELECT
dbo.CMPP_SendCentreMo.id,
dbo.CMPP_SendCentreMo.SendCentreID,
dbo.CMPP_SendCentreMo.Mo,
case isnull(dbo.CMPP_SendCentreMo.Stat,2) when 'DELIVRD' then '成功' when 2 then '未知' else '失败' end as Stat,
-- 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 [id],[SendType], [SendCentreID], [Mo], [Stat], [SendDate], [Port], [Service_ID], [FeeType], [FeeCode], [Msg_Content]
from [ViewCMPP_SendCentreMo]
where SendType = '扣费'
order by id asc加了order by,为什么就那么慢了
查询时
是先排序后再加上条件
还是先查条件后再排序呢我猜想,查询时应该时先排序后再加上条件
因我是用内联查询,加 order by id asc时,查询时要一行一行的去查,如果一下子找到,查询就很快
如果一直查不到,就会全表查询,所就慢了
至于为什么"普通"快就是因为表中"普通"的记录与"扣费"记表比7:1
"普通"记录占大部分就是在Order by ID asc 中再加入一个字段排序
如上面我就加了
Order by SendCentreID asc,ID asc
这样子查询时,只用了几十毫秒下面有两个通用的分页存储过程单表分页查询
http://hi.baidu.com/lrxin0630/blog/item/e96e15d42a93990aa18bb7dd.html
联合分页查询
http://hi.baidu.com/lrxin0630/blog/item/96c71fcb5849a8f652664fdf.html
总结:在使用Order by ID desc/asc 时,尽可能的在中间多加一个字段进行排序谢谢各位的回答