在很多的资料中都描述说SQLSERVER的存储过程较普通的SQL语句有以下优点:
存储过程只在创造时进行编译即可,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据 库的使用效率,减少程序的执行时间,这一点在较大数据量的数据库的操作中是非常重要的。在代码上看,SQL语句和程序代码语句的分离,可以提高程序代码的 可读性。
存储过程可以设置参数,可以根据传入参数的不同重复使用同一个存储过程,从而高效的提高代码的优化率和可读性。
安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类:
系统存储过程:以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
扩展存储过程 以XP_开头,用来调用操作系统提供的功能
exec master..xp_cmdshell ‘ping 10.8.16.1’
用户自定义的存储过程,这是我们所指的存储过程常用格式
模版:Create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement
解释:output:表示此参数是可传回的
with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。
但是最近我们项目组中有人写了一个存储过程,其计算时间为1个小时47分钟,而有的时候运行时间都超过了两个小时,同事描述说如果将存储过程中的语句拿出来直接运行也就10分钟左右就运行完毕,我没当回事,但是今天我自己写的存储过程也遇到了这个问题,在查找资料后原因终于找到了原因,原来是Parameter sniffing问题。
下面看我是如何将运行一个小时以上的存储过程优化成在一分钟之内完成的:
原存储过程
CREATE PROCEDURE [dbo].[pro_ImAnalysis_daily]
@THEDATE VARCHAR(30)
AS
BEGIN
IF @THEDATE IS NULL
BEGIN
SET @THEDATE=CONVERT(VARCHAR(30),GETDATE()-1,112);
END
DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;
INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)
SELECT AA.THEDATE,ALLUSER,NEWUSER
FROM
( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER
FROM FACT
WHERE THEDATE=@THEDATE
GROUP BY THEDATE
) AA
LEFT JOIN
(SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER
FROM FACT T1
WHERE NOT EXISTS(
SELECT 1
FROM FACT T2
WHERE T2.THEDATE<@THEDATE
AND T1.USERID=T2.USERID)
AND T1.THEDATE=@THEDATE
GROUP BY THEDATE
) BB
ON AA.THEDATE=BB.THEDATE);
GO
每日执行:exec pro_ImAnalysis_daily @thedate=null
耗时:1小时47分~2小时13分
经过查找资料,原因如下(由于源文是一篇英文,有些地方写的我不是特别清楚,原文见http://groups.google.com/group/microsoft.public.sqlserver.server/msg/ad37d8aec76e2b8f?hl=en&lr=&ie=UTF-8&oe=UTF-8):
在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。在上面的例子中,SQL在编译的时候并不知道@thedate的值是多少,所以它在执行执行计划的时候就要进行大量的猜测。假设传递给@thedate的参数大部分都是非空字符串,而FACT表中有40%的thedate字段都是null,那么SQL Server就会选择全表扫描而不是索引扫描来对参数@thedate制定执行计划。全表扫描是在参数为空或为0的时候最好的执行计划。但是全表扫描严重影响了性能。
假设你第一次使用了Exec pro_ImAnalysis_daily @thedate=’20080312’那么SQL Server就会使用20080312这个值作为下次参数@thedate的执行计划的参考值,而不会进行全表扫描了,但是如果使用@thedate=null,则下次执行计划就要根据全表扫描进行了。
有两种方式能够避免出现“Parameter sniffing”问题:
(1)通过使用declare声明的变量来代替参数:使用set @variable=@thedate的方式,将出现@thedate的sql语句全部用@variable来代替。
(2) 将受影响的sql语句隐藏起来,比如:
a) 将受影响的sql语句放到某个子存储过程中,比如我们在@thedate设置成为今天后再调用一个字存储过程将@thedate作为参数传入就可以了。数据分析培训
b) 使用sp_executesql来执行受影响的sql。执行计划不会被执行,除非sp_executesql语句执行完。
c) 使用动态sql(”EXEC(@sql)”来执行受影响的sql。
采用(1)的方法改造例子中的存储过程,如下:
ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]
@var_thedate VARCHAR(30)
AS
BEGIN
declare @THEDATE VARCHAR(30)
IF @var_thedate IS NULL
BEGIN
SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);
END
SET @THEDATE=@var_thedate;
DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;
INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)
SELECT AA.THEDATE,ALLUSER,NEWUSER
FROM
( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER
FROM FACT
WHERE THEDATE=@THEDATE
GROUP BY THEDATE
) AA
LEFT JOIN
(SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER
FROM FACT T1
WHERE NOT EXISTS(
SELECT 1
FROM FACT T2
WHERE T2.THEDATE<@THEDATE
AND T1.USERID=T2.USERID)
AND T1.THEDATE=@THEDATE
GROUP BY THEDATE
) BB
ON AA.THEDATE=BB.THEDATE);
GO
测试执行速度为10分钟,我又检查了一下这个SQL,发现这个SQL有问题,这个SQL使用了not exists,在一个大表里面使用not exists是不太明智的,所以,我又对这个sql进行了改进,改成如下:
ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]
@var_thedate VARCHAR(30)
AS
BEGIN
declare @THEDATE VARCHAR(30)
IF @var_thedate IS NULL
BEGIN
SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);
END
SET @THEDATE=@var_thedate;
DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;
INSERT RPT_IM_USERINFO_DAILY(THEDATE,ALLUSER,NEWUSER)
select @thedate as thedate,
count(distinct case when today>0 then userid else null end) as alluser,
count(distinct case when dates=0 then userid else null end) as newuser
from
(
select userid,
count(CASE WHEN thedate>=@thedate then null else thedate end) as dates,
count(case when thedate=@thedate then thedate else null end) as today
from FACT
group by userid
)as fact
GO
测试结果为30ms以下。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
最近我发现一个绝招,用DeepSeek AI处理Excel数据简直太爽了!处理速度嘎嘎快! 平常一整天的表格处理工作,现在只要三步就能搞 ...
2025-04-01你是否被统计学复杂的理论和晦涩的公式劝退过?别担心,“山有木兮:统计学极简入门(Python)” 将为你一一化解这些难题。课程 ...
2025-03-31在电商、零售、甚至内容付费业务中,你真的了解你的客户吗? 有些客户下了一两次单就消失了,有些人每个月都回购,有些人曾经是 ...
2025-03-31在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11