存储过程 临时表 表数据量大而只查询最近几天的数据 分区表 效率(急，跪求高手)

修炼成精 2009-04-17 10:21:05
请教，这个存储过程创建临时表，将查询到的数据放入临时表，然后返回临时表的数据
我的疑问是：表News_Price数据量上百万，而我只查5天内的数据，怎样处理查询效率快？1.对表News_Price加个日期字段的索引有效吗？2.还是新建表b存储5天内的数据？3.这个存储过程中创建临时表，并查询数据将其插入临时表是不是效率太差了？4.如果用其他方案，比如分区表，是不是得动大手术？
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



...全文
544 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
修炼成精 2009-04-27
  • 打赏
  • 举报
 回复
从17w条数据表里查询几十条数据，需要多少时间？
基于2表联合查询
strong1333 2009-04-27
  • 打赏
  • 举报
 回复
为什么不用 行内数据集函数 呢？
效果应该比用存储过程返回 表 好吧
不懂，才学 几天
老本 2009-04-24
  • 打赏
  • 举报
 回复
1 加时间戳字段
2 建索引
3 建视图，可不采用临时表加job的方式

修炼成精 2009-04-23
  • 打赏
  • 举报
 回复
引用的那个非聚集索引：
CREATE NONCLUSTERED INDEX [Pk_NewsPriceQasID] ON [dbo].[news_Price]
(
[QasID] ASC
)
INCLUDE ( [ID],
[Price],
[Createdate],
[CenterFlag],
[Bak],
[RunCircs],
[Count],
[Repertory]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
这里的include是什么？
请高手指明对哪个字段建立什么索引！谢谢！
修炼成精 2009-04-23
  • 打赏
  • 举报
 回复
set statistics time on
select * from news_Price where
DATEDIFF(month, CreateDate, Getdate()) <= 1
这里对日期作非聚集索引，但没有用到这个索引，用的是对news_Price另一个字段的非聚集索引
改成
CreateDate>=dateadd(month,-1,Getdate())
或者改成
CreateDate>= convert(varchar(7),dateadd(month,-1,Getdate()),120) + '-01'
AND CreateDate < convert(varchar(7),dateadd(month,1,Getdate()),120) + '-01'
都是！
Yang_ 2009-04-23
  • 打赏
  • 举报
 回复
1.对表News_Price加个日期字段的索引有效吗？
效果不明显，因为你的语句有问题

AND DATEDIFF(month, CreateDate, Getdate()) <= 1
应该改为
AND CreateDate >= convert(varchar(7),dateadd(month,-1,Getdate()),120) + '-01'
AND CreateDate < convert(varchar(7),dateadd(month,1,Getdate()),120) + '-01'

2.还是新建表b存储5天内的数据？
不需要，你的查询很多问题，子查询太多，估计索引也没用好，做好优化能提高速度，分表会带来维护成本。

3.这个存储过程中创建临时表，并查询数据将其插入临时表是不是效率太差了？
同问题1、2回答

4.如果用其他方案，比如分区表，是不是得动大手术？
同问题1、2回答
Q315054403 2009-04-23
  • 打赏
  • 举报
 回复
哪位江湖人士有雅兴慢慢调试，呵呵
修炼成精 2009-04-22
  • 打赏
  • 举报
 回复
楼上，请问查询“最近5天数据”是基于日期判断的，表b只有id 怎么查？
-晴天 2009-04-22
  • 打赏
  • 举报
 回复
如果要这样做,可以考虑建一个只有ID列的B表,在A表建一触发器,当向A表插入记录时将ID插入到B表.
每天凌晨(最好是1时而不是0时,经验)将B表中5天前的ID删除.
查询时由B表的ID连接到A表获取5天内的记录.
这样可能比较节省一点.
修炼成精 2009-04-20
  • 打赏
  • 举报
 回复
不用触发器和作业（太占用服务器，而且sql服务器里好像没那么多权限啊）
我在网页里做：
新建表b存储最近5天数据
插入数据的时候，同时插入到新建表b
表b保留最近5天的数据，得定时（00:00:00）删除createdate>(day,-5,getdate())

请问，这个用存储过程实现插入效率好么？得给createdate建立什么索引？（聚集、非聚集索引、唯一索引、复合索引）
我目前的那个存储过程（见top），是基于几个表的。请问如何优化，感激指教！
让你望见影子的墙 2009-04-17
  • 打赏
  • 举报
 回复
1.对表News_Price加个日期字段的索引有效吗？2.还是新建表b存储5天内的数据？3.这个存储过程中创建临时表，并查询数据将其插入临时表是不是效率太差了？4.如果用其他方案，比如分区表，是不是得动大手术？

1、对查询的字段加索引时必须的
2、第二种方案可以使用。建立一个job定时把b中的数据放到a表（现在的表）中
3、3、1使用触发器
3.2使用job

百万数据，应该不大，还不至于要用到分区表的地步。
Roc_Lee 2009-04-17
  • 打赏
  • 举报
 回复
晚上建个job执行一下，每天临晨执行。
修炼成精 2009-04-17
  • 打赏
  • 举报
 回复
但我这里旧数据还得要，只是经常查5天内的

把旧的数据分出去，具体怎么做？恳请大侠指教
1.首先建新表b结构跟表News_Price完全相同，并为其建立与其他表的关系约束之类
2.将5天内的数据插入表b
3.1为表News_Price插入数据是，同时插入表b
3.2每天凌晨0：00:00执行delete 表b where createdate=dateadd(day, -6, getdate())

是不是得用触发器啊？具体3.1和3.2怎么做啊？
kkun_3yue3 2009-04-17
  • 打赏
  • 举报
 回复
我的疑问是：
表News_Price数据量上百万，而我只查5天内的数据，怎样处理查询效率快？
1.对表News_Price加个日期字段的索引有效吗？
2.还是新建表b存储5天内的数据？
3.这个存储过程中创建临时表，并查询数据将其插入临时表是不是效率太差了？
4.如果用其他方案，比如分区表，是不是得动大手术？


1,有效
2,临时表也可以,实表也可以,表变量也成
3,临时表本身的效率问题可以忽略,但要妥善使用才行
4,分区表不是特别复杂,可以考虑使用


如果经常要查询该表,则建议将无效数据存储到另外一张表中,专用来存储历史数据

粗浅的理解,不对之处,还请指正
修炼成精 2009-04-17
  • 打赏
  • 举报
 回复
sqlserver agent已经启动，但是还是出现那个错误
我的sql补丁好像是最新的了
百度无果
望高手指点——新建 作业错误如上！
wzy_love_sly 2009-04-17
  • 打赏
  • 举报
 回复
把sql补丁打全，作业好象和windows的framework有关，不要把他卸载
wzy_love_sly 2009-04-17
  • 打赏
  • 举报
 回复 
作业的操作



企业管理器 

--管理 

--SQL Server代理 

--右键作业 

--新建作业 

--"常规"项中输入作业名称 

--"步骤"项 

--新建 

--"步骤名"中输入步骤名 

--"类型"中选择"Transact-SQL 脚本(TSQL)" 

--"数据库"选择执行命令的数据库 

--"命令"中输入要执行的语句: 

                      EXEC 存储过程名 ... --该存储过程用于创建表 



--确定 

--"调度"项 

--新建调度 

--"名称"中输入调度名称 

--"调度类型"中选择你的作业执行安排 

--如果选择"反复出现" 

--点"更改"来设置你的时间安排  





然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 



设置方法: 

我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
修炼成精 2009-04-17
  • 打赏
  • 举报
 回复
新建作业,填个名称，确定——错误：无法将类型为"Microsoft.SqlServer.Management.Smo.SimpleObjectKey"的对象强制转化为类型"Microsoft.SqlServer.Management.Agent.JobObjectKey"。(Microsoft.SqlServer.Smo)
【徐葳】流式数据湖新秀-Apache Paimon
1、什么是Apache Paimon2、Paimon的整体架构3、Paimon的核心特点4、Paimon支持的生态5、基于Flink SQL操作Paimon6、基于Flink DataStream API 操作Paimon7、Paimon中的内部表和外部表8、Paimon中的分区表和临时表9、...
吐血整理50家企业真实大数据面试题！！学长实测，真实面试！！快收藏！
mysql查的也不慢吧），然后问了我你认为多少的数据量适合用HBase（我说的几百万） （4）结合你这个HBase里存储的数据，说一下你们的rowkey怎么设计的（这块说的不好，因为前面说的不好，感觉这边说就强行设计了） ...
mysql整理
一InnoDB记录存储结构和数据页面结构 1.为什么不能直接更新磁盘上的数据？ ​ 因为磁盘随机读写的性能是最差的，所以直接更新磁盘文件，必然导致我们的数据库完全无法抗下任何一点点稍微高 并发一点的场景 ​ 所以...
Redis 入门到精通
缓存就是数据交换的缓冲区（称作Cache），是临时存贮数据（使用频繁的数据）的地方。当用户查询数据，首先在缓存中寻找，如果找到了则直接执行，如果找不到则去数据库中查找。
我在CSDN参与的3000个帖子
2：100分急求，随机输出十个小写字母，但是，要求这十个字母不相同 3：求Sn=a+aa+aaa+…+aaa…a（n个a）之值 4：数组题 望高手帮忙！ 5：呵呵，来推荐一下我的网站，本站提供大量当今流行的免费的音乐和免费电影，...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章