
在很多的资料中都描述说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
基于 SPSS 的 ROC 曲线平滑调整方法与实践指南 摘要 受试者工作特征曲线(ROC 曲线)是评估诊断模型或预测指标效能的核心工具, ...
2025-08-25神经网络隐藏层神经元个数的确定方法与实践 摘要 在神经网络模型设计中,隐藏层神经元个数的确定是影响模型性能、训练效率与泛 ...
2025-08-25CDA 数据分析师与数据思维:驱动企业管理升级的核心力量 在数字化浪潮席卷全球的当下,数据已成为企业继人力、物力、财力之后的 ...
2025-08-25CDA数据分析师与数据指标:基础概念与协同逻辑 一、CDA 数据分析师:数据驱动时代的核心角色 1.1 定义与行业价值 CDA(Certified ...
2025-08-22Power Query 移动加权平均计算 Power Query 移动加权平均设置全解析:从原理到实战 一、移动加权平均法的核心逻辑 移动加权平均 ...
2025-08-22描述性统计:CDA数据分析师的基础核心与实践应用 一、描述性统计的定位:CDA 认证的 “入门基石” 在 CDA(Certified Data Analy ...
2025-08-22基于 Python response.text 的科技新闻数据清洗去噪实践 在通过 Python requests 库的 response.text 获取 API 数据后,原始数据 ...
2025-08-21基于 Python response.text 的科技新闻综述 在 Python 网络爬虫与 API 调用场景中,response.text 是 requests 库发起请求后获取 ...
2025-08-21数据治理新浪潮:CDA 数据分析师的战略价值与驱动逻辑 一、数据治理的多维驱动引擎 在数字经济与人工智能深度融合的时代,数据治 ...
2025-08-21Power BI 热力地图制作指南:从数据准备到实战分析 在数据可视化领域,热力地图凭借 “直观呈现数据密度与分布趋势” 的核心优势 ...
2025-08-20PyTorch 矩阵运算加速库:从原理到实践的全面解析 在深度学习领域,矩阵运算堪称 “计算基石”。无论是卷积神经网络(CNN)中的 ...
2025-08-20数据建模:CDA 数据分析师的核心驱动力 在数字经济浪潮中,数据已成为企业决策的核心资产。CDA(Certified Data Analyst)数据分 ...
2025-08-20KS 曲线不光滑:模型评估的隐形陷阱,从原因到破局的全指南 在分类模型(如风控违约预测、电商用户流失预警、医疗疾病诊断)的评 ...
2025-08-20偏态分布:揭开数据背后的非对称真相,赋能精准决策 在数据分析的世界里,“正态分布” 常被视为 “理想模型”—— 数据围绕均值 ...
2025-08-19CDA 数据分析师:数字化时代的价值创造者与决策智囊 在数据洪流席卷全球的今天,“数据驱动” 已从企业战略口号落地为核心 ...
2025-08-19CDA 数据分析师:善用 Power BI 索引列,提升数据处理与分析效率 在 Power BI 数据分析流程中,“数据准备” 是决定后续分析质量 ...
2025-08-18CDA 数据分析师:巧用 SQL 多个聚合函数,解锁数据多维洞察 在企业数据分析场景中,单一维度的统计(如 “总销售额”“用户总数 ...
2025-08-18CDA 数据分析师:驾驭表格结构数据的核心角色与实践应用 在企业日常数据存储与分析场景中,表格结构数据(如 Excel 表格、数据库 ...
2025-08-18PowerBI 累计曲线制作指南:从 DAX 度量到可视化落地 在业务数据分析中,“累计趋势” 是衡量业务进展的核心视角 —— 无论是 “ ...
2025-08-15Python 函数 return 多个数据:用法、实例与实战技巧 在 Python 编程中,函数是代码复用与逻辑封装的核心载体。多数场景下,我们 ...
2025-08-15