为什么存储过程会提高性能 [英] Why Store Procedure improve performence

问题描述

在上一次采访中，interviwer问我为什么存储过程可以提高性能，但是我没有告诉他正确的答案，请帮助我....
为什么可以提高性能?

In my last interview , interviwer asked me why stored procedure improve performence but I didnt tell him correct answer please help me....
why it improve performenbce

推荐答案

亲爱的拉文德拉，

使用存储过程的原因:

减少网络流量-您必须通过网络发送SQL语句.使用sproc，您可以批量执行SQL，这也更加有效.


缓存查询计划-第一次执行sproc时，SQL Server创建一个执行计划，并将其缓存以供重用.这对于频繁运行的小型查询尤其有效.


使用输出参数的能力-如果您发送返回一行的内联SQL，则只能获取一个记录集.使用sproc，您可以将它们作为输出参数取回，这要快得多.

权限-当您发送内联SQL时，您必须向用户授予对表的权限，这比仅授予执行sproc的权限要授予更多的访问权限


逻辑分离-删除生成SQL的代码并将其隔离在数据库中.


无需重新编译即可进行编辑的功能-这可能会引起争议.您可以在存储过程中编辑SQL，而无需重新编译应用程序.


查找使用表的地方-对于sproc，如果要查找引用特定表的所有SQL语句，则可以导出sproc代码并进行搜索.这比尝试在代码中查找要容易得多.


优化-使用存储过程时，DBA可以更轻松地优化SQL并优化数据库.查找丢失的索引等很容易.


SQL注入攻击-正确编写的内联SQL可以抵御攻击，但是存储过程对于这种保护是更好的


有关更多详细信息，请访问以下链接: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficiency-in-general-than-inline-statements-on-mo [

Dear Ravindra,

Reasons for using stored procedures:

Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.


Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.


Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.

Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc


Separation of logic -- remove the SQL-generating code and segregate it in the database.


Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.


Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.


Optimization -- It''s easier for a DBA to optimize the SQL and tune the database when sprocs are used. It''s easier to find missing indexes and such.


SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection


For more details please follow this link: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mo[^]

好...请参见此处 [ ^ ].

但是，主要要点是它们以编译形式存储.

Well...see here[^].

The main point however is that they are stored in compiled form.

这篇关于为什么存储过程会提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！