请教,表a数据量太大，而我平时只查询5天内的数据，请问怎么提高查询效率?

[修炼成精]

请教
表a数据量太大，而我平时只查询5天内的数据，请问怎么提高查询效率?

[yygyogfny]

建索引了吗

[yygyogfny]

贴表结构看看,还有查询条件

[csdyyr]

try:
时间字段建索引
where datefield>dateadd(day, -5, getdate()) and datefield<=getdate()

[sparklerl]

以时间字段为索引,倒叙

如果还不能解决就做套状表了

[幸运的意外]

在经常查询的字段上加上索引吧。这样会快一些。另外，可以吧一定时间之前的数据备份后，从表a里删除。减少不必要的记录扫描。

[claro]

[Quote=引用 6 楼 zhoudelun 的回复:]
引用 3 楼 csdyyr 的回复:
try:
时间字段建索引
where datefield>dateadd(day, -5, getdate()) and datefield <=getdate()

后边的没必要吧？表里的数据怎么可能比当天还大呢？
请问我对这个表做索引，并 order by desc
在存储过程中关联另外几个表，做查询，会造成这种索引无效的结果么？
[/Quote]如果经常读，建议加。
另外a表数据量大，建议进行分区。

[bhtfg538]

CREATE PARTITION FUNCTION [NAME] DATETIME AS RANGE LEFT(1,100,1000);


清楚部分历史数据，导出到其他库

[修炼成精]

1.楼上诸位都想删除旧数据
但旧数据还是有用的，我只是经常查询最近5天的
2.用分区表，会不会对表结构大动手术啊？我这个用到了相关的几个表，是通过一个存储过程查询最近5天的数据，请看我新帖
http://topic.csdn.net/u/20090417/10/3f13475a-9a81-4b1e-9e5e-68505bd43ddd.html
谢谢指点

[長胸為富]

查询条件中经常用的几列建立非聚集复合索引

[修炼成精]

不用触发器和作业（太占用服务器，而且sql服务器里好像没那么多权限啊）
我在网页里做：
新建表b存储最近5天数据
插入数据的时候，同时插入到新建表b
表b保留最近5天的数据，得定时（00:00:00）删除createdate>(day,-5,getdate())

请问，这个用存储过程实现插入效率好么？得给createdate建立什么索引？（聚集、非聚集索引、唯一索引、复合索引）
我目前的那个存储过程（见top），是基于几个表的。请问如何优化，感激指教！

[fan22176391]

表a数据量太大，而我平时只查询5天内的数据

我想你的查询应该只是针对个别查询，建议可以将你需要的字段存入临时表中，一般的表结构在时间这个栏位上肯定会有索引。然后再在临时表中根据自己的需要建新的索引。查询完删除临时表，这样可以避免在基表上乱加索引和预防查询时间过长导致基表死锁。

[修炼成精]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFiveDayPrice]
@ProductID int,
@flag bit
AS
BEGIN
create table #temp1
(
QasUnit varchar(50),
monthAvgPrice float(20),
weekAvgPrice float(20),
price1 float(20),
price2 float(20),
price3 float(20),
price4 float(20),
price5 float(20),
Bak text,
DisplayOrder int
)

declare
@QasID INT
,@CreateDate Datetime
,@d Datetime
,@Q INT

declare @SortID int
--只选择查看一个地区
if(@flag=1)
begin
select @QasID = 0, @CreateDate = '1900-1-1'

select * Into #list from news_Price as a where
exists(select * from news_QasUnit b where ProductID=@ProductID and a.QasID=b.QasID)
AND DATEDIFF(month, CreateDate, Getdate()) <= 1
order by CreateDate DESC

UPDATE #list SET @Q = @QasID, @QasID = QasID, @d = @CreateDate, @CreateDate = CreateDate, CreateDate = CASE WHEN @QasID = @Q AND DATEDIFF(d, @d, @CreateDate) = 0 THEN '1900-1-1' ELSE CreateDate END

insert into #temp1 select QasUnit=(select top 1 c.QasUnit from news_QasUnit as c where c.QasID=#list.QasID )
,avgMonth = avg(CASE WHEN DATEDIFF(month, CreateDate, Getdate()) = 1 THEN Price ELSE NULL END)
,avgWeek = avg(CASE WHEN DATEDIFF(week,CreateDate,Getdate()) = 1 THEN Price ELSE NULL END)
,yesprice5=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list.QasID and DateDiff(day,CreateDate,GetDate())=4 order by CreateDate desc)
,yesprice4=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list.QasID and DateDiff(day,CreateDate,GetDate())=3 order by CreateDate desc)
,yesprice3=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list.QasID and DateDiff(day,CreateDate,GetDate())=2 order by CreateDate desc)
,yesprice2=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list.QasID and DateDiff(day,CreateDate,GetDate())=1 order by CreateDate desc)
,yesprice1=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list.QasID and DateDiff(day,CreateDate,GetDate())=0 order by CreateDate desc)
,bak=(select top 1 bak from news_Price where news_Price.QasID=#list.QasID and DATEDIFF(d,CreateDate,Getdate()) = 0 order by CreateDate desc)
,DisplayOrder=(SELECT top 1 DisplayOrder from news_QasUnit where news_QasUnit.QasID=#list.QasID )
from #list where CreateDate > '2000-1-1' group by #list.QasID order by DisplayOrder

drop table #list
end
--选择查看全国报价
else
begin
Declare Cur Cursor For
select sortid from dbo.news_sort where parentid = @ProductID
Open Cur
Fetch Cur Into @SortID
While @@FETCH_STATUS=0
BEGIN
select @QasID = 0, @CreateDate = '1900-1-1'

select * Into #list2 from news_Price as a where
exists(select * from news_QasUnit b where ProductID=@SortID and a.QasID=b.QasID)
AND DATEDIFF(month, CreateDate, Getdate()) <= 1
order by CreateDate DESC

UPDATE #list2 SET @Q = @QasID, @QasID = QasID, @d = @CreateDate, @CreateDate = CreateDate, CreateDate = CASE WHEN @QasID = @Q AND DATEDIFF(d, @d, @CreateDate) = 0 THEN '1900-1-1' ELSE CreateDate END

insert into #temp1 select QasUnit=(select top 1 c.QasUnit from news_QasUnit as c where c.QasID=#list2.QasID )
,avgMonth = avg(CASE WHEN DATEDIFF(month, CreateDate, Getdate()) = 1 THEN Price ELSE NULL END)
,avgWeek = avg(CASE WHEN DATEDIFF(week,CreateDate,Getdate()) = 1 THEN Price ELSE NULL END)
,yesprice5=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list2.QasID and DateDiff(day,CreateDate,GetDate())=4 order by CreateDate desc)
,yesprice4=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list2.QasID and DateDiff(day,CreateDate,GetDate())=3 order by CreateDate desc)
,yesprice3=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list2.QasID and DateDiff(day,CreateDate,GetDate())=2 order by CreateDate desc)
,yesprice2=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list2.QasID and DateDiff(day,CreateDate,GetDate())=1 order by CreateDate desc)
,yesprice1=(select top 1 price from News_Price where CenterFlag=2 and News_Price.QasID=#list2.QasID and DateDiff(day,CreateDate,GetDate())=0 order by CreateDate desc)
,bak=(select top 1 bak from news_Price where news_Price.QasID=#list2.QasID and DATEDIFF(d,CreateDate,Getdate()) = 0 order by CreateDate desc)
,DisplayOrder=(SELECT top 1 DisplayOrder from news_QasUnit where news_QasUnit.QasID=#list2.QasID )
from #list2 where CreateDate > '2000-1-1' group by #list2.QasID order by DisplayOrder
Fetch Cur Into @SortID
drop table #list2
end
Close Cur
Deallocate cur

end

select * from #temp1

end

-------------------------------------------------------
是通过这个获取最近5天数据的！
探寻这个存储过程的性能问题！
我想新建个表b存储最近5天数据：
1.应该给这个表b建立怎样的索引（特别id和日期字段应该怎样设置）？
2.如何定时删除5天以外的数据呢？
不新建表b，用视图或存储过程查询 最近5天的数据效果怎样？

[fcuandy]

聚集索引含所要过滤的日期列,(当然,也可包含其它列,看具体情况). 日期列为索引前导列.

3kw的表里查5天的数据,也只是几秒.

[修炼成精]

可否告知 具体创建 “日期列为索引前导列”？怎么弄这个地方
你的意思不需要创建新表，谢谢！

[修炼成精]

USE [Oil_08]
GO
/****** 对象: Index [PK_news_Price] 脚本日期: 04/21/2009 15:36:09 ******/
ALTER TABLE [dbo].[news_Price] ADD CONSTRAINT [PK_news_Price] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
这个是id的聚集索引，应该怎样改？恳请指教！
是不是：
USE [Oil_08]
GO
/****** 对象: Index [PK_news_Price] 脚本日期: 04/21/2009 15:36:09 ******/
ALTER TABLE [dbo].[news_Price] ADD CONSTRAINT [PK_news_Price] PRIMARY KEY CLUSTERED
(
[createdate] desc,[ID] ASC--这里对日期倒序吧？但id呢？也应该倒序吧？
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

[acmilan1984]

建个历史表比如 a_bak 表结构跟A一样，每天0点10分开始将表A内5天前数据导入到A_BAK里面，然后删除A5天前数据。
查询时判断，如果日期范围大于5天则 A_BAK 与 A 做 UNION ALL，然后在级联出去查询。否则，只需要A与其他表级联查询。

[修炼成精]

既然id和createdate都是变大 的，对id 建立索引和对createdate有什么区别呢？迷惑
经常比较的是createdate字段，应该对其建立怎样的索引呢？

对原表只是插入数据频繁
对新表b要 每天删除5天外的数据，如何实现

这两个表应该建立怎样的索引？

[meheartfly]

[Quote=引用 3 楼 csdyyr 的回复:]
try:
时间字段建索引
where datefield>dateadd(day, -5, getdate()) and datefield <=getdate()
[/Quote]
严重同意，没有比这更好的方法了

[hery2002]

2005的话,尽量分区吧,
分区后按照时间字段建立索引.

[flairsky]

建个时间上的聚集索引

如果能按时间做分区表，那更好