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秒不？分不够可以再加。
...全文
145 点赞 收藏 19
写回复
19 条回复
切换为时间正序
当前发帖距今超过3年，不再开放新的回复
发表回复
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
等高手
 回复 赞
相关推荐
SQL Server存储过程编写和优化措施 一、适合读者对象：数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。 　二、介绍：在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个...
存储过程与SQL的对比？ 存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，在执行过程时便可节省此开销。 2、降低网络开销 存储过程调用时只需用提供存储过程名和...
SqlServer存储过程实现及拼接sql的注意点 由于服务器原因，我不能直接在数据访问层写SQL，所以只好抽离出来放到存储过程里面。 这里就出现了一个问题，我花费了好久才弄通！ 　其实就是很简单的一个SQL语句： 　update table1 set field1=value1,field2 ...
SQL存储过程优化参考 收藏的存储过程优化参考，包括： 存储过程编写经验和优化措施.doc 优化SQL语句和存储过程.pdf 希望对大家有点参考价值
sql server存储过程优化 博主写的很好！ 不过有些地方我还不太理解为什么会使性能更好，慢慢研究。 SQL Server 存储过程的几种常见写法分析，我们该用那种写法
Sql语句优化及存储过程 1.　怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。 2.：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多...
SQL 优化存储过程 数据库优化语句
sql优化的几种方式 一、为什么要对SQL进行优化 我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化...
DV6.2 For SQL存储过程版 由动网先锋的DV6.2 For Access 版本修改而成。 系统改用强大的SQL后台数据库，利用SQL数据库强大的存储过程处理技术，优化系统资源。能够承受极大的网站访问压力。同时添加了很多
ORACLE SQL 优化 存储过程 PROCEDURE 本人五年的SQL开发经验和存储过程开发样例、以及SQL优化要点，数据库DB_LINK,数据库审计等，绝对物超所值，欢迎大家下载。
sql server 性能优化之nolock 其实除了index的建立之外，当我们在下SQL Command时，在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。 　不过有一点千万要注意的就是，WITH (NOLOCK)的SQL SELECT...
SQL Server 存储过程与实例 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回...
sql2000分页存储过程优化 sql2000分页存储过程优化 列出3个sql2000分页方法 对比执行时间
sql存储过程实例–动态根据表数据复制一个表的数据到另一个表 创建一个 sub_track的存储过程实现： -- 创建一个 名为sub_track的存储过程 CREATE PROCEDURE sub_track() begin declare i int; -- 定义 循环变量i set @imei =0; -- 定义imei最后两位 set @t_count=0; -- 定义...
Infortrend ESVA存储针对SQL数据库优化 本文讲介绍该系统应用于Microsoft SQL Server环境下的优化配置过程。 　解析ESVA的可扩展架构 　预留多少容量给数据库以满足当前的需求，对管理员来说未来数据的增长是容量规划方面的一个大问题。预留的存储过多将...
EDA/PLD中的SQL Server存储过程编写和优化措施 一、适合读者对象：数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。 　二、介绍：在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个...
SQL Server 2016 查询存储性能优化小结 作为一个DBA，排除SQL Server问题是我们的职责之一，每个月都有很多人给我们带来各种不能解释却要解决的性能问题。 我就多次听到，以前的SQL Server的性能问题都还好且在正常范围内，但现在一切已经改变，SQL Server...
MySQL存储过程的优化实例 前言 在数据库的开发过程中，经常会遇到复杂的业务逻辑和对...下面介绍某一个MySQL存储过程优化的整个过程。 在本文中，需要被优化的存储过程如下： drop procedure if exists pr_dealtestnum; delimiter // create
sql优化和存储过程 如何让系统运行更流畅 良好的数据库设计和使用是必不可少的
TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储...
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-20 10:10
社区公告
暂无公告