SQL 存储过程优化

chengzhicn 2010-10-20 10:10:57
写了一个存储过程用来获得指定日期(参数)的所有股票的流通市值,但其执行时间时长时短,长的话得3-5分钟,短的话只要3-4秒.
以下是用到的表


T_STOCK_INDEX 股票信息表,存有股票ID和其所对应的公司ID,有 4,679 条记录
{
int f_id 股票ID
int f_gsid 公司ID
}
有一个建在f_id(ASC) 上的非聚集索引 MSCCSPK_20090801013535921


T_GS_SHARE 公司股本变动表,存有所有公司的股本变动情况,有 18,912 条记录
{
int F_GSID 公司ID
int F_DATE 变动日期
int F_JNPTG 变动后的流通股本
}
有一个建在 F_GSID (ASC), F_DATE (ASC) 上的聚集索引 (IX_T_GS_SHARE_GSID_DATE) 和一个建在 F_DATE (ASC) 上的索引 (IX_T_GS_SHARE_DATE)


T_HQ_INDEX 行情表,存有所有股票的日线行情,有 6,621,671 条记录
{
int F_ID 股票ID
int F_DATE 日期
double F_CLOSE 收盘价
}
有一个建在 F_ID (ASC), F_DATE (ASC) 上的聚集索引 (IDINDEX) 和一个建在 F_DATE (ASC) 上的索引 (DATEINDEX)


取流通市值需要先读取T_STOCK_INDEX获得所有股票及其对应的公司ID,然后再查找T_GS_SHARE,找到指定日期(参数)前的最后一次股本变动,然后再将这个股本乘以行情表里指定日期的收盘价得到流通市值

这里有几点需要注意的:
1 如果指定的日期(参数)比较靠前,可能T_STOCK_INDEX里的某些公司还没有上市,并且这些公司在T_GS_SHARE中的所有记录的 F_DATE 都将比指定日期(参数)大,最终的结果里也不能出现这些没上市的公司.
2 T_HQ_INDEX里的行情并不是每天都有,如遇到股票停牌就没有停牌当天的数据,所以在计算流通市值时需要查找指定日期(参数)前的最后一个收盘价

以下是存储过程代码

ALTER PROCEDURE [dbo].[sp_GetNegCap]
-- Add the parameters for the stored procedure here
@Date int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET FMTONLY OFF;
if @Date=0 or @Date is null set @Date=99999999
SELECT sinfo.f_id, share.F_JNPTG * hq.F_CLOSE AS NegCap, share.F_DATE, hq.F_CLOSE, share.F_JNPTG
FROM dbo.T_STOCK_INDEX AS sinfo INNER JOIN
dbo.T_GS_SHARE AS share ON sinfo.f_gsid = share.F_GSID INNER JOIN
(SELECT F_ID, F_DATE, F_CLOSE
FROM dbo.T_HQ_INDEX AS hqinfo
WHERE (F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_HQ_INDEX AS temp
WHERE (F_ID = hqinfo.F_ID) and F_DATE<=@Date))) AS hq ON hq.F_ID = sinfo.f_id
WHERE (share.F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_GS_SHARE AS temp
WHERE (F_GSID = share.F_GSID) and F_DATE<=@Date))
AND (hq.F_DATE IN
(SELECT MAX(F_DATE) AS maxdate
FROM dbo.T_HQ_INDEX AS temp
WHERE (F_ID = hq.F_ID) and F_DATE<=@Date))
ORDER BY sinfo.f_id
END


该存储过程有的时候执行只要3-4秒,有的时候要3-5分钟。
不过有如下规律:
1:使用 SQL Server Management Studio ,如果本次执行时间为3-4秒,那么在不执行其他操作的情况下不论执行多少遍都是3-4秒,如果本次是3-5分钟,那么在不执行其他操作的情况下不论执行多少遍都是3-5分钟。
2:如果在 SQL Server Management Studio 执行ALTER语句修改存储过程,即使一个字符都不改,只是ALTER一下,都有可能改变存储过程的执行时间(估计有90%的概率执行需要3-5分钟)
3:重启 Sql Server 未发现执行时间有变化
4:一次在没有ALTER存储过程的情况下,对查询做了次“在数据库引擎优化顾问中分析查询”后,执行时间由3-5分钟变为3-4秒,但是该操作以后都没能重现
5:即使在 SQL Server Management Studio 执行只要3-4秒,在C++ Builder或C# 中执行依然可能需要3-5分钟。当然也出现过在SQL Server Management Studio 、C++ Builder、C# 执行都只需要3-4秒的情况,不过我今天已经试了N次了,到目前为止这种情况都还没有出现,如果出现,我也不会发这个帖子了。
6:在 SQL Server Management Studio 对查询 "exec sp_GetNegCap" 显示查询计划,3-4秒的实际执行计划和3-5分钟的实际执行计划不同。

3-5分钟的执行计划


3-4秒的执行计划


不知道各位牛人有啥办法能把执行时间固定到3-4秒不?分不够可以再加。
...全文
209 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
SQLCenter 2010-10-21
  • 打赏
  • 举报
回复
那 #13 的会不会跑几分钟呢?

我详细看了一下你原来的执行计划,3-4sec基本上是HASH匹配,3-5min基本上LOOP匹配。
这个就很难预期查询优化使用什么匹配,除非使用联接提示。

明天再来了。
chengzhicn 2010-10-21
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 sqlcenter 的回复:]
SQL code
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_IND……
[/Quote]

这个不错,执行只要10秒,结果也OK
不过改成


select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
join
(select F_ID, max(F_DATE)F_DATE from T_HQ_INDEX where F_DATE<=@Date group by F_ID) d
on c.F_ID=d.F_ID and c.F_DATE=d.F_DATE
join
(select f_gsid, max(F_DATE)F_DATE from T_GS_SHARE where F_DATE<=@Date group by f_gsid) e
on e.f_gsid=b.f_gsid and e.F_DATE=b.F_DATE


跑了两分钟,一条结果都还没出来。

另外很奇怪为啥同样的代码,有的时候执行3-4秒,有的时候3-5分钟呢?是什么决定了这个执行时间的差异?
chengzhicn 2010-10-21
  • 打赏
  • 举报
回复
#13 10秒结果就出来了, #16 跑了10分钟都没出结果
chengzhicn 2010-10-21
  • 打赏
  • 举报
回复
我试了下将13楼的代码放到存储过程中,运行需要3分钟,我放到存储过程里的看了下执行计划,先通过DATEINDEX找出F_DATE<@Date的记录(6百多万条),然后通过IDINDEX将这六百多万条的收盘价找出来,再将这6百多万条记录排序,排序完成后对这6百多万条记录group得到4238行记录,时间主要都花在给6百多万条记录排序上了。
山书生 2010-10-20
  • 打赏
  • 举报
回复
学习~@.@
SQLCenter 2010-10-20
  • 打赏
  • 举报
回复
再慢的话,T_GS_SHARE也按照T_HQ_INDEX的方式来处理,万把记录用子查询应该问题也不大。
SQLCenter 2010-10-20
  • 打赏
  • 举报
回复
select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
join
(select F_ID, max(F_DATE)F_DATE from T_HQ_INDEX where F_DATE<=@Date group by F_ID) d
on c.F_ID=d.F_ID and c.F_DATE=d.F_DATE
where
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE)
chengzhicn 2010-10-20
  • 打赏
  • 举报
回复
TO:SQLCenter
又查询试了次,用时52秒,
估计是由于 T_HQ_INDEX 比较大,有6百多万行记录,每天新增4000多行地缘故
chengzhicn 2010-10-20
  • 打赏
  • 举报
回复
TO:SQLCenter
我将代码改了一下


declare @Date int

set @Date = 20101020

select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
b.F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
where
c.F_DATE <= @Date and
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE) and
not exists (select 1 from T_HQ_INDEX where F_ID=c.F_ID and F_DATE<=@Date and F_DATE>c.F_DATE)


这次执行结果没有问题了,但是用时53秒
SQLCenter 2010-10-20
  • 打赏
  • 举报
回复
1、结果集中的F_DATE应该是最后一次股本变动的日期
--
是不是不考虑@Date参数,就是实际上最后一次变动日期?

2、这点忽略了,弥补也很容易。
chengzhicn 2010-10-20
  • 打赏
  • 举报
回复
不好意思,这个帖子是有点长,但是我一点头绪都没有,所以把可能有关的信息都列出来了。

TO:SQLCenter
执行速度很快,但是有些问题:
1 结果集中的F_DATE应该是最后一次股本变动的日期
2 一般股本变动当天股票都会停牌,所以当天是没有行情记录的,所以加上 c.F_DATE = @Date 这个条件,结果就只有几百条。
dawugui 2010-10-20
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 fredrickhu 的回复:]
太长了 有点晕
[/Quote]同晕同晕.
SQLCenter 2010-10-20
  • 打赏
  • 举报
回复
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE)
SQLCenter 2010-10-20
  • 打赏
  • 举报
回复
declare @Date int

select
c.F_ID,
b.F_JNPTG*c.F_CLOSE NegCap,
@Date F_DATE,
c.F_CLOSE,
b.F_JNPTG
from
T_STOCK_INDEX a
join
T_GS_SHARE b on a.f_gsid=b.F_GSID
join
T_HQ_INDEX c on a.f_id=c.F_ID
where
c.F_DATE = @Date and
b.F_DATE<=@Date and
not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE<b.F_DATE)
--小F-- 2010-10-20
  • 打赏
  • 举报
回复
太长了 有点晕
claro 2010-10-20
  • 打赏
  • 举报
回复
学习。

关注。
yhtapmys 2010-10-20
  • 打赏
  • 举报
回复
友情UP
等高手

22,300

社区成员

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

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