精华内容
下载资源
问答
  • SQL存储过程优缺点

    千次阅读 2018-07-28 17:17:22
    存储过程是由一些SQL语句控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它,也可以从另一个过程或...

    概要:

    存储过程是由一些SQL语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它,也可以从另一个过程或触发器调用。
    存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。
    根据返回值类型的不同,我们可以将存储过程分为三类:返回记录集的存储过程,返回数值的存储过程(也可以称为标量存储过程),以及行为存储过程。顾名思义,返回记录集的存储过程的执行结果是一个记录集,典型的例子是从数据库中检索出符合某一个或几个条件的记录;返回数值的存储过程执行完以后返回一个值,例如在数据库中执行一个有返回值的函数或命令;最后,行为存储过程仅仅是用来实现数据库的某个功能,而没有返回值,例如在数据库中的更新和删除操作。


    例子:

    大型数据库一般都支持存储过程,合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中,但是又不可能一条SQL完成,这时你需要如下3步操作:
    a:将A表数据全部取出到客户端;
    b:计算出要更新的数据;
    c:将计算结果更新到B表。
    如果采用存储过程你可以将整个业务逻辑封装在存储过程里,然后在客户端直接调用存储过程处理,这样可以减少网络交互的成本。


    优点:

    1. 减少网络通信量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
    2. 较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
    3. 标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
    4. 作为一种安全机制来充分利用:使用存储过程使您能够增强对执行计划的重复使用,通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
    5. 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。
    6. 可维护性高:更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

    缺点:

    1. 不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。
    2. 学习成本高,DBA一般都擅长写存储过程,但并不是每个程序员都能写好存储过程,除非你的团队有较多的开发人员熟悉写存储过程,否则后期系统维护会产生问题。
    3. 业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。
    4. 存储过程和常用应用程序语言不一样,它支持的函数及语法有可能不能满足需求,有些逻辑就只能通过应用程序处理。
    5. 如果存储过程中有复杂运算的话,会增加一些数据库服务端的处理成本,对于集中式数据库可能会导致系统可扩展性问题。
    6. 为了提高性能,数据库会把存储过程代码编译成中间运行代码(类似于java的class文件),所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后,存储过程需要重新编译才能生效,在高并发应用场景,一般都是在线变更结构的,所以在变更的瞬间要同时编译存储过程,这可能会导致数据库瞬间压力上升引起故障(Oracle数据库就存在这样的问题)。

    总结:

    普通业务逻辑尽量不要使用存储过程,定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。存储过程可以快速解决问题,但是移植性、维护性、扩展性不好,它有时会约束软件的架构,约速程序员的思维,在系统没有性能问题时不建议用存储过程。如果你要完成的功能只是一次或有限次的工作,如数据订正、数据迁移等等,存储过程也可以拿上用场。
    如果你的系统很小,并且有50%的开发人员熟练掌握PL/SQL,人员结构稳定,那存储过程可以减少很多代码量,并且性能不错。当系统变复杂了,开发人员多了,存储过程的弊端就会呈现。


    参考:

    [1] https://blog.csdn.net/yzsind/article/details/6059209
    [2] https://blog.csdn.net/stevendbaguo/article/details/28587419
    [3] https://blog.csdn.net/jackmacro/article/details/5688687
    [4] https://www.cnblogs.com/mark-chan/p/5384139.html

    展开全文
  • 存储过程优缺点分析

    2020-12-15 13:34:47
    存储过程优缺点存储过程优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划(存储过程在首次运行时将被编译,...
  • 存储过程和sql语句的优缺点

    千次阅读 2013-03-19 10:31:23
    答:存储过程优缺点: 优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划(存储过程在首次运行时将被编译,...

    http://3871584.blog.163.com/blog/static/33526564201232501919316/

    答:存储过程的优缺点:

    优点:
    1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。
    2.执行计划(存储过程在首次运行时将被编译,这将产生一个执行计划-- 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。)缓存改善性能。
    ……..但sql server新版本,执行计划已针对所有 T-SQL 批处理进行了缓存,而不管它们是否在存储过程中,所以没比较优势了。
    3.存储过程可以用于降低网络流量,存储过程代码直接存储于数据库中,所以不会产生大量T-sql语句的代码流量。
    4.使用存储过程使您能够增强对执行计划的重复使用,由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划,并只需插入更新的参数值。
    5.可维护性高,更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
    6.代码精简一致,一个存储过程可以用于应用程序代码的不同位置。
    7.更好的版本控制,通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具,您可以轻松地恢复到或引用旧版本的存储过程。
    8.增强安全性:
    a、通过向用户授予对存储过程(而不是基于表)的访问权限,它们可以提供对特定数据的访问;
    b、提高代码安全,防止 SQL注入(但未彻底解决,例如,将数据操作语言--DML,附加到输入参数);
    c、SqlParameter 类指定存储过程参数的数据类型,作为深层次防御性策略的一部分,可以验证用户提供的值类型(但也不是万无一失,还是应该传递至数据库前得到附加验证)。
    缺点:
    1.如果更改范围大到需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用,等等,这时候估计比较繁琐了。
    2.可移植性差
    由于存储过程将应用程序绑定到 SQL Server,因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要,则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。
    Sql语句灵活,可移植性强,查询速度比存储过程慢些
    展开全文
  • 存储过程通过明确地将查询的可变部分转换为参数来增进执行计划的可重用性。这使执行计划在查询以可变部分的相同或不同值重新提交时可以被重用。... 存储过程的重编译既有好处又有缺点,有时候,为查询考虑一个新的处理

    存储过程通过明确地将查询的可变部分转换为参数来增进执行计划的可重用性。这使执行计划在查询以可变部分的相同或不同值重新提交时可以被重用。存储过程包含一组复杂的SQL语句,使生成存储过程的执行计划的代价有些高。因此,通常重用存储过程的执行计划来代替生成新计划是有利的。但是,有时候现有的计划可能不适用或者在重用期间可能不能提供最佳的处理策略。SQL Server重编译存储过程中的语句来生成一个新的执行计划,以解决这个问题。

    一、重编译的好处

      存储过程的重编译既有好处又有缺点,有时候,为查询考虑一个新的处理策略而不是重用现有计划可能更经济。特别是表(或对应的统计)中数据的分布变化或者表中添加了新的索引时。SQL Server 2008中的重编译在语句级别上。语句级重编译降低开销是因为只重编译单独的语句,而不是存储过程中的所有语句。而在SQL Server 2005以前重编译方法导致存储过程一次又一次地被完全重编译。

      创建一个存储过程并执行,代码如下:

    CREATE PROC dbo.getPerson
    AS
    SELECT Id,Name,Age FROM PersonHunderThousand
    WHERE Name = '夏娩好'
    
    EXEC dbo.getPerson

      目前执行当前存储过程的执行计划如下:

      

      假如我们建了一个覆盖索引之后:

    CREATE NONCLUSTERED INDEX IX_Name ON PersonHunderThousand(Name) INCLUDE(Age)

      明显,再次执行该查询,使用索引的执行计划更有利:

      

      SQL Server自动重编译了存储过程以有效使用索引。

      在这种情况下,花费额外的CPU周期重新编译存储过程建立更好的执行计划是有利的。

      SQL Server自动侦测需要重新编译现有计划的条件。SQL Server根据特定的规则确定现有计划需要重新编译的时机。如果存储过程的一个特定实现进入重编译规则的范围(执行计划老化、SET选项变化等),则存储过程将在每次符合重编译要求时重新编译,SQL Server可能不能生成更好的执行计划。

    二、重编译存储过程的缺点

      在SQL Server Profiler中,SP_Recompile事件用于跟踪语句重编译。也可以使用SQL:StmtRecompile事件,这是存储过程事件。

      上面创建的索引是有益于存储过程里面的语句的,这次我们先删除刚刚创建的覆盖索引,然后换成一个对查询语句里无益的索引:

    EXEC dbo.getPerson
    GO
    CREATE NONCLUSTERED INDEX IX_Name ON PersonHunderThousand(InCome)
    GO
    EXEC dbo.getPerson

      我们来看SQL Server Profiler监控的输出:

      

      我们看到,重新编译对该存储过程无益。但是不幸地,它进入了导致SQL Server在每次执行时重新编译存储过程的条件范围。这使存储过程缓冲效率降低,并且将CPU周期浪费在重新生成相同的计划上。因此,知道导致存储过程重新编译的条件,并且在实现存储过程时用一切办法避免这些条件是很重要的。当然,这个例子有点牵强,谁没事会一天到晚建索引啊。

    三、确认导致重编译的语句

      SQL Server可以重新编译存储过程中的单条语句或整个存储过程。因而,为了找到重编译的起因,确认不能重用现有计划的SQL语句是很重要的。

      可以使用Profiler工具来跟踪存储过程重编译。也可以使用Profiler工具来确认导致存储过程重编译的语句。

       分析存储过程重编译的事件和数据列:

    事件数据列
    SP:CompletedEventClass
    SP:RecompileTextData
    SP:StartingEventSubClass
    SP:StmtCompleted(可选)SPID
    SP:StmtStaring(可选)StartTime

       考虑下面简单的存储过程:

    CREATE PROC dbo.InsertPerson
    AS
    CREATE TABLE t1(c1 INT)
    INSERT INTO t1(
    c1
    )VALUES(42);    --数据修改导致重编译

      输出如下:

      

      从输出可以看到,有一个重编译事件(SP:Recompile),表示该存储过程经受了重编译。当存储过程第一次执行时,SQL Server编译存储过程并生成一个执行计划。

      执行计划值维持在易失性的内存中,它们在SQL Server重启时被抛弃。在服务器重启后的存储过程下一次执行时,SQL Server再次编译存储过程并且生成执行计划。这些编译不被看做存储过程的重编译,因为该计划不存在于缓冲中用于重用,SP:Recompile事件表示计划已经存在但是不能被重用。为了了解那条语句引起的重编译,需要查看SP:Recompile事件中的TextData列,它明确地说明被重编译的语句。还可以使用SP:StmtStarting事件和重编译事件的组合来确认导致重编译的存储过程语句。SP:Recompile事件紧接在SP:StmtStarting事件之后发生表明该存储过程语句导致重编译。使用TextData列更简单,但是在非常复杂的过程中,使用SP:StmtStarting事件可能更有意义。

      注意,在存储过程重编译之后,导致重编译的存储过程语句再次启动以执行新的计划。可以使用SP:StmtStarting事件或SP:StmtCompleted事件以确认导致重编译的存储过程语句。

    ------------------------------------------------------------------------------------

     

    为了改进性能,分析重编译的起因很重要。往往,重编译可能并不需要,可以避免它以改进性能。了解导致重编译发生的不同条件有助于评估重编译的起因,并决定在重编译不必要时避免它的方法。

      存储过程重编译在以下情况下发生:

    • 存储过程语句中引用的常规表、临时表或视图的架构变化。架构变化包括表的元数据或表上索引的变化;
    • 常规或临时表的列上的绑定(如默认/规则)变化。
    • 表索引或列上的统计的变化超过一定的阈值。
    • 存储过程编译时一个对象不存在,但是在执行期间创建(延迟对象解析);
    • SET选项变化;
    • 执行计划老化并释放;
    • 对sp_recompile系统存储过程的显式调用。
    • 显式使用RECOMPILE子句;

      可以在Profiler中看到这些变化。原因由SP:Recompile事件的EventSubClass数据列值指出:

    EventSubClass描述
    1常规表或试图的结构或绑定变化
    2统计变化
    3在执行期间创建存储过程计划中不存在的对象
    4SET选项变化
    5临时表架构或绑定变化
    6远程行集的架构或绑定变化
    7FOR BROWSE许可变化
    8查询通知环境变化
    9MPI视图变化
    10游标选项变化
    11调用WITH RECOMPILE选项

     

    一、架构或绑定变化

      当一个视图、常规表或临时表的架构或绑定变化时,现有的存储过程执行计划将作废。执行任何引用这些对象的语句之前该存储过程必须重编译。SQL Server自动侦测这种情况并且重编译存储过程。

      因此,要避免架构变化,尽量不要在存储过程中涉及到临时表,索引,字段的操作,即不要交替使用DML、DDL语句。

      在存储过程中,DDL语句通常用于创建局部临时表以及修改他们的架构(包括添加索引)。这样做可能影响现有计划的有效性,并且可能在引用该表的存储过程语句被执行时导致重编译。

      如:

    CREATE PROC sp_Test
    AS
    CREATE TABLE #TB1(ID INT,DSC NVARCHAR(50))
    INSERT INTO #TB1(ID,DSC) 
    SELECT TOP 100 ID,NAME FROM PersonTenThousand;    --第1次重编译
    SELECT * FROM #TB1 AS TT    --第2次重编译
    CREATE CLUSTERED INDEX IXID ON #TB1(ID);
    SELECT TOP 10 * FROM #TB1; --第3次重编译
    CREATE TABLE #TB2(c1 INT);
    SELECT * FROM #TB2;    --第4次重编译
    GO
    EXEC sp_Test
    GO
    EXEC sp_Test

      SQL Server Profiler跟踪输出:

      

      可以看到存储过程被重编译4次。

    •   第一次重编译来自于多了个表#TB1,现有自行计划不包含表#TB1的任何信息。
    •   第二次重编译来自于临时表数据的变化;
    •   第三次重编译来自于临时表架构变化,多了个索引;
    •   第四次冲编译多了表#TB2,因为是新建的表,现有执行计划没有关于#TB2的任何信息;

    二、统计变化

      SQL Server记录表的变化数量。如果变化数量超过重编译阈值(RT),SQL Server自动在存储过程中引用该表时更新统计。当侦测到自动更新统计的条件时,SQL Server自动重新编译存储过程并更新统计。

      RT由一组取决于表是永久表或临时表(不是表变量)以及表中的行数的公式来确定。表10-3显示了基本的公式,可以确定由于数据变化引起的语句重编译预期时间。

    表类型公式
    永久表

    如果n(行数)<=500,则RT=500;

    如果n>500,则RT=500+.2*n

    临时表

    如果n<6,则RT=6;

    如果6<=n<=500,则RT=500;

    如果n>500,则RT=500+.2*n

      统计变化引起的重编译可能生成一个和前一个计划相同的计划,在这种情况下,重编译是没有必要的,如果开销较大则应避免。

      避免统计的变化而引起的重编译有两个方法:

    •   使用KEEPFIXED PLAN选项;
    •   禁用该表上的自动更新统计特性;

      1、使用KEEPFIXED PLAN选项

      SQL Server提供KEEPFIXED PLAN选项来避免因为统计变化引起的重编译。

    CREATE PROC GetPerson
    AS
    SELECT * FROM Person
    WHERE Id = 1
    OPTION(KEEPFIXED PLAN);

      如,像上面这种写法,执行存储过程就不会再因为表Person上的统计变化而引起重编译。

      2、禁用表上的自动更新统计

      也可以通过禁用相关表上的自动更新统计来避免统计更新引起的重编译。(不过这种方法就好比,脚疼就把整个脚砍掉,不可取)。

      例如,可以关闭表Person上的自动更新统计

    EXEC sp_autostats 'Person','OFF'

      虽然这种方式可以避免重编译,但是使用这种技术应该非常小心,因为过时的统计可能对基于开销的优化器有负面的影响,如果禁用统计的自动更新,应该有一个定期更新统计的SQL任务。

    三、延迟对象解析

      存储过程通常动态创建然后访问数据库对象。当这样的存储过程第一次执行时,第一个执行计划不包含关于运行时创建的对象的信息。因此,在第一个执行计划中,对这些对象的处理策略被延迟到存储过程的运行时。

      当执行一个引用这些对象的DML语句时,存储过程被重新编译以生成一个包含该对象处理策略的新计划。

      在存储过程中可以创建常规表和局部临时表来保存中间结果。由于延迟对象解析引起的存储过程重编译对于常规表和局部临时表来说有所不同。

      1、由于常规表引起的重编译

      为了理解在存储过程中创建常规表所致的存储过程重编译问题,考虑以下实例:

    CREATE PROC dbo.p1
    AS
    CREATE TABLE dbo.p1_t1(c1 INT);        --存储过程开始时表不存在
    SELECT * FROM dbo.p1_t1;              --导致重编译
    DROP TABLE dbo.p1_t1                  --结束后删除该表
    GO
    EXEC dbo.p1    --第一次执行
    EXEC dbo.p1   --第二次执行

      SQL Server Profiler输出:

      

      第一次执行该存储过程时,执行计划在存储过程实际执行之前生成。

      但是如果在存储过程创建之前存储过程中创建的表不存在,引用该表的SELECT语句尚不会有执行计划。因此为了执行SELECT语句,存储过程必须重编译。可以看到,在第二次执行时SELECT语句被重编译,在第一次执行期间卸载存储过程中的表并没有卸载保存在过程缓冲中的存储过程计划。SQL Server考虑为其表架构的一次变化,因此SQL Server在存储过程后执行SELECT语句之前重新编译存储过程。因此,看到第一次的重编译依然发生在BatchStarting之后。

      2、由于局部临时表引起的重编译

      大部分时候,在存储过程中创建局部临时表而不是常规表。为了礼节局部临时表对存储过程重编译的不同影响,修改前面的实例,只用一个临时表替换常规表。

    CREATE TABLE dbo.p1 
    AS
    CREATE TAVLE #p1_t1(c1 INT)    --指定本地临时表
    SELECT * FROM #p1_t1        --在第一次执行时导致重编译
    DROP TABLE #p1_t1
    GO
    EXEC dbo.p1    --第一次执行
    EXEC dbo.p1    --第二次执行

      因为局部临时表在存储过程执行结束时自动卸载,所有没有必要明确地卸载临时表。但是,在局部临时表工作完成之后马上卸载它是一个好的编程习惯。

      最好分开点击,这样SQL Server Profiler生成的监控比较清晰:

      

       从监控输出可以看到,第一次执行时存储过程被重编译。对应的EventSubClass值支出的重编译起因和常规表上的一样,但是,存储过程在重新执行时不被重编译,这和常规表不同。

      存储过程后续执行期间的局部临时表架构与前一次执行时保持一致。局部临时表不可用于存储过程的范围之外,所以其架构无论执行多少次都是一致的,因此,SQL Server在存储过程后续执行期间确定能安全地重用现有计划,避免了重编译。

      临时表会引起存储过程重编译,想要避免可以使用表变量代替。

    四、SET选项变化

      在存储过程的执行计划取决于环境设置。如果环境设置在存储过程中变化,则SQL Server在每次执行时重编译存储过程。

    CREATE PROC dbo.p1
    AS
    SELECT 'a' + null + 'b';    --第一次拼接
    SET CONCAT_NULL_YIELDS_NULL OFF;
    SELECT 'a' + null + 'b';    --第二次拼接
    SET ANSI_NULLS OFF;
    SELECT 'a' + null + 'b';    --第三次拼接
    GO
    EXEC dbo.p1    --第一次执行
    EXEC dbo.p1    --第二次执行

      SQL Server Profiler输出如下:

      

      因为SET NOCOUNT没有修改环境设置,不像前面看到的用于修改ANSI设置的SET语句,SET NOCOUNT不会导致存储过程重编译。

      在存储过程中修改SET选项导致SQL Server在执行SET语句后面的语句之前重编译该存储过程。因此,这个存储过程分别在SET语句后被重编译两次。

      但如果在这之后,在执行存储过程:

    EXEC dbo.p1 --第三次执行
    EXEC dbo.p1 --第四次执行
    EXEC dbo.p1 --第五次执行

      都不会再重编译,因为那些内容现在已经成为了执行计划的一部分。

      因此,如果想要避免由于SET选项变化引起的存储过程重编译时,尽量不要在存储过程中设置SET选项。

      另外,SET NOCOUNT选项是一个例外,它没有修改环境设置,不像前面示例中的那样会导致重编译。

    五、执行计划老化

      SQL Server通过维护缓冲中执行计划的寿命来管理过程缓冲的大小,如果一个存储过程长时间未被重新执行,执行计划的寿命字段将下降为0,内存短缺时将把该计划从缓冲中删除。当这种情况发生并且存储过程被重新执行时,将生成一个新计划并将其缓冲到过程缓冲中。但是,如果系统中有足够的内存,未使用的计划在内存压力增加之前不会被删除。

    六、显式调用sp_recompile系统存储过程

      SQL Server还提供了sp_recompile系统存储过程来手工标记需要重编译的存储过程。这个系统存储过程可以在表、视图、存储过程或触发器上调用。如果在存储过程或触发器上调用,则该存储过程或触发器在下次执行时被重编译。在表或视图上调用标记所有调用该表/视图的存储过程和触发器在下次执行时重新编译。

      如在表Person上调用sp_recompile,则所有调用Person表的存储过程和触发器被标记为需要重编译,在下次执行时重新编译

    sp_recompile 'Person'

      也可以使用sp_compile来使用sp_executesql执行时指定撤销重用现有计划。注意在 SQL Server Profiler集合中,指定重编译存储过程的记录事件 SP:CacheInsert 而不是事件 SP:Recompile。指定表才是SP:Recompile。

      示例:

    CREATE PROC GetPerson
    @Id int
    AS
    SELECT * FROM PersonTenThousand 
    WHERE ID = @Id
    GO
    EXEC GetPerson @Id = 9876    --连续执行3次,都没有重编译
    sp_recompile 'GetPerson'     --指定重编译存储过程
    EXEC GetPerson @Id = 9876    --这次重编译了
    sp_recompile 'PersonTenThousand'    --指定对引用表的存储过程重编译
    EXEC GetPerson @Id = 9876    --这次也重编译了

      捕获如下:

      

    七、显示使用WITH RECOMPILE子句

      SQL Server允许使用CREATE PROCRDURE或EXECUTE的RECOMPILE子句显式地重编译一个存储过程。这些方法降低了计划可重用性的效率,所以只应该在一些特殊的场合使用它们。

      1、CREATE PROCEDURE语句的RECOMPILE子句

      有时候,存储过程的计划需求可能随着调用存储过程的参数值变化而变化。在这种情况下,重用使用不同参数值的该计划可能降低存储过程的性能,可以使用CREATE PROCEDURE语句的RECOMPILE子句来强制每次执行存储过程都生成一个新计划(仅仅新生成执行计划,并非重编译整个存储过程)。

      示例:

    CREATE PROC GetPerson
    @Id int
    WITH RECOMPILE
    AS
    SELECT * FROM PersonTenThousand 
    WHERE ID = @Id
    GO
    EXEC GetPerson @Id = 9876    --每次都重新生成执行计划

      SQL Server Profiler监控输出如下:

      

      2、Execute语句的RECOMPILE子句

      存储过程中的特定参数值可能需要不同的执行计划,可以在执行存储过程时动态采用WITH RECOMPILE,对特定一次执行重新生成新计划。

    CREATE PROC GetPerson
    @Id int
    AS
    SELECT * FROM PersonTenThousand 
    WHERE ID = @Id
    GO
    EXEC GetPerson @Id = 9876    --不重新生成计划
    EXEC GetPerson @Id = 9876 WITH RECOMPILE    --新生成计划

      SQL Server Profiler输出:

      

      当存储过程使用WITH RECOMPILE子句执行时,将临时生成一个新计划。这个新的计划不会被缓冲,并且不会影响现有计划。当存储过程不使用RECOMPILE子句执行时,该计划和往常一样被缓冲。仅仅影响一次执行,这与CREATE PROCDURE不同。因为可以考虑创建不同的存储过程来代替使用CREATE PROCDURE时RECOMPILE。

    八、使用OPTIMIZE FOR查询提示

      尽管不总是能减少或消除重编译,但是使用OPTIMIZE FOR查询提示可以帮助你使用指定的参数值来编译计划,而不管调用的应用程序传入的参数值。

      如:

    CREATE PROC dbo.getPerson
    @Id INT
    AS
    SELECT * FROM Person
    INNER JOIN Province
    WHERE Person.Id = @Id
    OPTION (OPTIMIZE FOR(@Id = 1))    --指定使用Id为1的参数来生成执行计划

      执行:

    EXEC dbo.getPerson @Id = 1234 WITH RECOMPILE    --强制重编译
    EXEC dbo.getPerson @Id = 5678 WITH RECOMPILE    --强制重编译

      现在,执行存储过程都重编译了,但是每次都是根据Id为1来生成执行计划。不会因为传入的参数不同而改变。

     

    参考

    https://www.cnblogs.com/kissdodog/p/3431576.html

    https://www.cnblogs.com/kissdodog/p/3431749.html

    展开全文
  • 存储过程优缺点

    千次阅读 2019-05-20 16:21:33
    在维护公司的几年前的一个项目,虽说不上是历史悠久,但也有些年头了,采用三层架构,加上存储过程的这么一个老的架构,本来这个架构也...可以说被存储过程伤的很深啊,所以今天就来总结下存储过程优缺点。  ...

     

        在维护公司的几年前的一个项目,虽说不上是历史悠久,但也有些年头了,采用三层架构,加上存储过程的这么一个老的架构,本来这个架构也没什么问题,大部分系统都是这么干的,但是由于这个系统存储过程被滥用的问题,后期维护的时候,麻烦事就来了,任何涉及到数据库的调整,都得存储过程,实体类,SQL操作等相关的地方,都得改一通。可以说被存储过程伤的很深啊,所以今天就来总结下存储过程的优缺点。

      优点

      1. 运行速度:对于很简单的sql,存储过程没有什么优势。对于复杂的业务逻辑,因为在存储过程创建的时候,数据库已经对其进行了一次解析和优化。存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用,所以执行速度会比普通sql快。    

      2.  减少网络传输:存储过程直接就在数据库服务器上跑,所有的数据访问都在数据库服务器内部进行,不需要传输数据到其它服务器,所以会减少一定的网络传输。但是在存储过程中没有多次数据交互,那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。

      3. 可维护性:的存储过程有些时候比程序更容易维护,这是因为可以实时更新DB端的存储过程。  有些bug,直接改存储过程里的业务逻辑,就搞定了。 

      4. 增强安全性:提高代码安全,防止 SQL注入。这一点sql语句也可以做到。

      5. 可扩展性:应用程序和数据库操作分开,独立进行,而不是相互在一起。方便以后的扩展和DBA维护优化。

      缺点  

       1. SQL本身是一种结构化查询语言,但不是面向对象的的,本质上还是过程化的语言,面对复杂的业务逻辑,过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理,如果如果把业务逻辑全放在存储过程里面,违背了这一原则。

       2. 如果需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新调用,等等,这时候估计会比较繁琐了。

       3. 开发调试复杂,由于IDE的问题,存储过程的开发调试要比一般程序困难。     

       4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。

       5. 不支持群集,数据库服务器无法水平扩展,或者数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。

      总结

       1. 适当的使用存储过程,能够提高我们SQL查询的性能,

       2. 存储过程不应该大规模使用,滥用。

       3. 随着众多ORM 的出现,存储过程很多优势已经不明显。   4. SQL最大的缺点还是SQL语言本身的局限性——SQL本身是一种结构化查询语言,我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑,还是交给代码去处理吧。

    原文地址

    展开全文
  • 首先说存储过程吧: 存储过程: 是一组SQL语句集,从开头到结尾包含这许多的SQL语句,然后中间对于有需要的业务逻辑处理,进行逻辑控制。解决一些复杂的业务时比较实用。 比如说我在service层处理业务逻辑的虎,...
  • 通用存储过程分页(使用ROW_NUMBER()不使用ROW_NUMBER()两种情况)性能分析
  • 数据库使用存储过程优缺点

    千次阅读 2019-12-22 19:02:53
    做后台系统服务器开发,使用数据库,会使用存储过程吗?
  • 2.模块化的设计思想(分页存储过程,订单,转账....);PLSQL3.减少网络传输量;开发工具:1.sqlplus开发工具2.pl/sql developer开发工具;4.提高安全性;缺点:1.移植性不好; 5 /如果存储过程名已经被占用,两种...
  • 存储过程及游标的优缺点

    千次阅读 2018-11-23 17:18:57
    前言:相信很多小伙伴在学习阶段肯定使用过存储过程及...存储过程优缺点 优点  1. 运行速度:对于很简单的sql存储过程没有什么优势。对于复杂的业务逻辑,因为在存储过程创建的时候,数据库已经对其进行了一...
  • 存储过程 就是一些编译好了的SQL语句,这些SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后给这些代码块取一个名字,在用到这个功能的时候调用即可。 存储过程详解参考: ...
  • MySQL存储过程优缺点

    千次阅读 2018-05-14 11:10:11
    在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用了。有人直接就在正式服务器上修改存储过程,而没有经过完整的测试,后果非常严重。2.执行速度快...
  • 1.视图 (1).什么是视图? 视图(View)作为一种数据库对象,为用户提供了一个可以检索...视图并不是以一组数据的形式存储在数据库中,数据库中只存储视图的定义,而不存储视图对应的数据,这些数据仍存储在导出视图的基本
  • 存储过程概念存储过程是一些sql语句控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序通过存储过程名字调用,也可以从另一个存储过程或触发器调用。它的参数可以被传递返回,与应用程序中...
  • 存储过程和函数,是用来实现一组关于表操作的SQL语句、可重复地执行操作数据库的集合。 存储过程和函数可以简单的理解为一条或多条SQL语句的集合。存储过程和函数就是事先经过编译并存储在数据库中的一段SQL语句集合...
  • SQL存储过程使用介绍

    万次阅读 多人点赞 2017-04-25 13:54:13
    先将存储过程的相关介绍使用方法总结如下 ;1. 存储过程的概念存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次...
  • 使用视图和存储过程优缺点

    千次阅读 2017-03-18 17:58:47
    使用视图和存储过程优缺点  –以下内容整理自网络视图的优点:   简单性   视图不仅可以简化用户对数据的理解,也可以简化他们的操作,哪些经常使用的查询可以被定义为视图,从而使用户不必为以后的操作每次都...
  • MySQL存储过程和函数的区别与优缺点

    千次阅读 2017-08-10 15:24:00
    什么是存储过程和函数:存储过程可以简单的理解为一条或者多条sql语句的集合。函数数通常是数据库已定义的方法。存储过程和函数事先进行过编译。存储过程和函数执行不是由程序调用,也不是手动启动。而是由事件触发...
  • SqlServer存储过程详解

    千次阅读 2021-03-13 22:57:22
    存储过程:        存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句进行缓存,这样下次执 行的时候直接使用缓存中的语句,这样就可以...
  • 触发器和存储过程优缺点

    千次阅读 2013-11-21 10:31:08
    触发器和存储过程优缺点 (一) 缺点: 1、可移植性是存储过程和触发器最大的缺点。 2、占用服务器端太多的资源,对服务器造成很大的压力 3、不能做DDL。 4、触发器排错困难,而且数据容易造成不一致,后期...
  • 文章目录1. 存储过程和函数在实际项目中的... 存储过程缺点4.2. 创建存储过程 create procedure4.2.1. 参数 in、out、inout4.3. 调用存储过程 call4.4. 查看存储过程4.4.1. 查看存储过程的状态4.4.2. 查看存储过程
  • 本文给大家详细介绍了sqlserver的存储过程,包括存储过程的概念、优缺点、系统存储过程、用户自定义存储过程等方面的内容了,有需要的小伙伴可以参考下。
  • oracle数据库存储过程优缺点

    千次阅读 2019-11-06 10:56:02
    对于复杂的业务逻辑,因为在存储过程创建的时候,数据库已经对其进行了一次解析优化。存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用,所以执行速度...
  • sQL存储过程优缺点

    千次阅读 2017-01-04 11:33:19
    目前具体的项目中没有使用sql存储过程,都用的封装好的框架,简单说下存储过程优缺点。 优点: 1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,...
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    MySQL 存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存在数据库的数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 139,590
精华内容 55,836
关键字:

存储过程和sql优缺点