SQL 存储过程优化

[chengzhicn]

写了一个存储过程用来获得指定日期(参数)的所有股票的流通市值,但其执行时间时长时短,长的话得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秒不？分不够可以再加。

[yhtapmys]

友情UP
等高手

• 打赏
• 举报

回复 赞

[claro]

学习。

关注。

• 打赏
• 举报

回复 赞

[--小F--]

太长了 有点晕

• 打赏
• 举报

回复 赞

[SQLCenter]

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)

• 打赏
• 举报

回复 赞

[SQLCenter]

not exists (select 1 from T_GS_SHARE where F_GSID=b.F_GSID and F_DATE<=@Date and F_DATE>b.F_DATE)

• 打赏
• 举报

回复 赞

[dawugui]

[Quote=引用 4 楼 fredrickhu 的回复:]
太长了 有点晕
[/Quote]同晕同晕.

• 打赏
• 举报

回复 赞

[chengzhicn]

不好意思，这个帖子是有点长，但是我一点头绪都没有，所以把可能有关的信息都列出来了。

TO:SQLCenter
执行速度很快，但是有些问题：
1 结果集中的F_DATE应该是最后一次股本变动的日期
2 一般股本变动当天股票都会停牌，所以当天是没有行情记录的，所以加上 c.F_DATE = @Date 这个条件，结果就只有几百条。

• 打赏
• 举报

回复 赞

[SQLCenter]

1、结果集中的F_DATE应该是最后一次股本变动的日期
--
是不是不考虑@Date参数，就是实际上最后一次变动日期？

2、这点忽略了，弥补也很容易。

• 打赏
• 举报

回复 赞

[chengzhicn]

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秒

• 打赏
• 举报

回复 赞

[chengzhicn]

TO:SQLCenter
又查询试了次，用时52秒，
估计是由于 T_HQ_INDEX 比较大，有6百多万行记录，每天新增4000多行地缘故

• 打赏
• 举报

回复 赞

[SQLCenter]

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)

• 打赏
• 举报

回复 赞

[SQLCenter]

再慢的话，T_GS_SHARE也按照T_HQ_INDEX的方式来处理，万把记录用子查询应该问题也不大。

• 打赏
• 举报

回复 赞

[山书生]

学习~@.@

• 打赏
• 举报

回复 赞

[chengzhicn]

我试了下将13楼的代码放到存储过程中，运行需要3分钟，我放到存储过程里的看了下执行计划，先通过DATEINDEX找出F_DATE<@Date的记录（6百多万条），然后通过IDINDEX将这六百多万条的收盘价找出来，再将这6百多万条记录排序，排序完成后对这6百多万条记录group得到4238行记录，时间主要都花在给6百多万条记录排序上了。

• 打赏
• 举报

回复 赞

[chengzhicn]

#13 10秒结果就出来了， #16 跑了10分钟都没出结果

• 打赏
• 举报

回复 赞

[chengzhicn]

[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分钟呢？是什么决定了这个执行时间的差异？

• 打赏
• 举报

回复 赞

[SQLCenter]

那 #13 的会不会跑几分钟呢？

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

明天再来了。

• 打赏
• 举报

回复 赞