1.1w+
社区成员
6.8w+
社区内容
- 主页
向大侠求教：加了日期排序字段后，查询速度剧减，我该如何优化？谢谢！！！
向大侠求教：加了日期排序字段后，查询速度剧减，我该如何优化？谢谢！！！
Order by 后面跟上日期型字段a.postDate速度剧减，已经在该字段上加了索引，
还是很慢啊！这不是要命吗？如何跨越他？
select top 20
a.marketId,
a.subject,
b.companyName
from markets a inner Join company b on a.UserId=b.UserId
where
a.marketId = (select top 1 marketId from markets where userId=a.UserId order by marketId desc)
--not exists (select 0 from markets where userId=a.UserId and marketId < a.marketId)
Order by b.userType desc , a.postDate desc
Order by 后面跟上日期型字段a.postDate速度剧减，已经在该字段上加了索引，
还是很慢啊！这不是要命吗？如何跨越他？
select top 20
a.marketId,
a.subject,
b.companyName
from markets a inner Join company b on a.UserId=b.UserId
where
a.marketId = (select top 1 marketId from markets where userId=a.UserId order by marketId desc)
--not exists (select 0 from markets where userId=a.UserId and marketId < a.marketId)
Order by b.userType desc , a.postDate desc
...全文
张海霖 2005年08月06日
联合聚集索引
phantomMan 2005年06月11日
如果你需要经常取最新新闻时,此时,如果数据按段比较明显,比如上面你要查询每天每刻的最新记录,这是在时间上建立聚簇索引是最好的;
拓狼 2005年06月10日
表markets的UserID上建立了索引，但是company的UserId上没有建立索引，因为在查询的时候是用这两个表的Userid做连接的，由于只有一个索引，另一个没有索引，查询时company要进行全表扫描，故建议在company的UserID上也建立索引，速度会提高很多
mengzulin 2005年06月10日
不会，可能是你取到了加锁了的记录．
mengzulin 2005年06月10日
Sorry,I send wrong.
mengzulin 2005年06月10日
一年有54周,所以不用月份了
jsntcw 2005年03月17日
高手帮忙啊！
谢谢
谢谢
jsntcw 2005年03月17日
索引部份：
ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [PK_Markets] PRIMARY KEY CLUSTERED
(
[MarketId] DESC
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [DF_Markets_UserId] DEFAULT (0) FOR [UserID],
CONSTRAINT [DF_SupplyDemand_ProductID] DEFAULT (0) FOR [ProductID],
CONSTRAINT [DF_Markets_CategoryId] DEFAULT (0) FOR [CategoryId],
CONSTRAINT [DF_SupplyDemand_type] DEFAULT (1) FOR [type],
CONSTRAINT [DF_SupplyDemand_Price] DEFAULT (0) FOR [Price],
CONSTRAINT [DF_SupplyDemand_OfferExpire] DEFAULT (0) FOR [OfferExpire],
CONSTRAINT [DF_SupplyDemand_PostDate] DEFAULT (getdate()) FOR [PostDate],
CONSTRAINT [DF_SupplyDemand_ViewTimes] DEFAULT (0) FOR [ViewTimes],
CONSTRAINT [DF_Markets_check] DEFAULT (0) FOR [Audit],
CONSTRAINT [DF_Markets_HiddenContact] DEFAULT (0) FOR [HiddenContact]
GO
CREATE INDEX [IX_Markets_CategoryId] ON [dbo].[Markets]([CategoryId] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_UserID] ON [dbo].[Markets]([UserID] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_PostDate] ON [dbo].[Markets]([PostDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_Keys] ON [dbo].[Markets]([Keys]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [PK_Markets] PRIMARY KEY CLUSTERED
(
[MarketId] DESC
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Markets] WITH NOCHECK ADD
CONSTRAINT [DF_Markets_UserId] DEFAULT (0) FOR [UserID],
CONSTRAINT [DF_SupplyDemand_ProductID] DEFAULT (0) FOR [ProductID],
CONSTRAINT [DF_Markets_CategoryId] DEFAULT (0) FOR [CategoryId],
CONSTRAINT [DF_SupplyDemand_type] DEFAULT (1) FOR [type],
CONSTRAINT [DF_SupplyDemand_Price] DEFAULT (0) FOR [Price],
CONSTRAINT [DF_SupplyDemand_OfferExpire] DEFAULT (0) FOR [OfferExpire],
CONSTRAINT [DF_SupplyDemand_PostDate] DEFAULT (getdate()) FOR [PostDate],
CONSTRAINT [DF_SupplyDemand_ViewTimes] DEFAULT (0) FOR [ViewTimes],
CONSTRAINT [DF_Markets_check] DEFAULT (0) FOR [Audit],
CONSTRAINT [DF_Markets_HiddenContact] DEFAULT (0) FOR [HiddenContact]
GO
CREATE INDEX [IX_Markets_CategoryId] ON [dbo].[Markets]([CategoryId] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_UserID] ON [dbo].[Markets]([UserID] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_PostDate] ON [dbo].[Markets]([PostDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_Markets_Keys] ON [dbo].[Markets]([Keys]) ON [PRIMARY]
GO
jsntcw 2005年03月17日
表结构如下：marketid 上已建索引
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Markets]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Markets]
GO
CREATE TABLE [dbo].[Markets] (
[MarketId] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[CategoryId] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [tinyint] NOT NULL ,
[Subject] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Keys] [varchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Specification] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Pack] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DateCode] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Qty] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OfferExpire] [smallint] NOT NULL ,
[Note] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PostDate] [datetime] NOT NULL ,
[ViewTimes] [int] NOT NULL ,
[Audit] [bit] NOT NULL ,
[HiddenContact] [bit] NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Markets]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Markets]
GO
CREATE TABLE [dbo].[Markets] (
[MarketId] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[CategoryId] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[type] [tinyint] NOT NULL ,
[Subject] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Keys] [varchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Specification] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Pack] [varchar] (160) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Brand] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DateCode] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Qty] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OfferExpire] [smallint] NOT NULL ,
[Note] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PostDate] [datetime] NOT NULL ,
[ViewTimes] [int] NOT NULL ,
[Audit] [bit] NOT NULL ,
[HiddenContact] [bit] NOT NULL
) ON [PRIMARY]
GO
wudan8057 2005年03月17日
select top 20
a.marketId,
a.subject,
b.companyName
from markets a inner Join company b on a.UserId=b.UserId
where a.marketId = (select min(marketId) from markets where userId=a.UserId)
Order by b.userType desc , a.postDate desc
在markets表的marketid上建立索引.
最好把你的表结构贴出来
a.marketId,
a.subject,
b.companyName
from markets a inner Join company b on a.UserId=b.UserId
where a.marketId = (select min(marketId) from markets where userId=a.UserId)
Order by b.userType desc , a.postDate desc
在markets表的marketid上建立索引.
最好把你的表结构贴出来
jsntcw 2005年03月17日
up
jsntcw 2005年03月17日
up
jsntcw 2005年03月17日
up