参数化查询和存储过程 [英] Parameterized query and stored procedure

问题描述

对于插入，更新和删除等数据库操作，在性能和安全性方面更好，更快：参数化查询或存储过程？还有什么时候使用DataSet？

For Database operations like insert, update and delete which is better and fast in terms of performance and security: Parameterized Query or Stored Procedure ? And also when to use DataSet ?

推荐答案

参数化查询与存储过程

方面没有区别表现或安全（差不多）。 SQL Server缓存两者的查询计划。您可以使用与存储过程相同的方式在模式/表上设置安全性。 Sure SP为您提供了更多的抽象级别，但这是您需要维护的另一个方面。此外，对于SP，您很容易将业务逻辑放在那里，而您不应该这样做。我使用查询（或LINQ）。



数据集

不要使用它。查看实体框架或其他ORM。如果这对你来说太重了，那里就有微型ORM。将数据放在POCO中是无价的。

Parameterized Query vs Stored Procedure
There is no difference in respect to performance or security (almost). SQL server caches query plans for both. You can setup security on schemas/tables the same way as you'd do on stored procedures. Sure SP gives you one more level of abstraction but it's one more thing you need to maintain. Furthermore with SPs you are tempted to put business logic there which you shouldn't. I'd use queries (or LINQ).

Dataset
Don't use that. Have a look on Entity Framework or other ORM. If that's too heavy weight for you there are micro ORMs out there. Having your data in POCOs is priceless.

在我看来，执行存储过程与执行单个语句之间存在很大差异。在两者中你都应该使用参数化，所以从单一语句的角度来看差异不是那么大。



但是，考虑一种情况，您需要执行多个语句才能获得所需的结果。如果语句位于调用端，则需要单独执行它们，因此每次执行都意味着往返数据库并返回。这可能是非常耗时的时间。



另一种情况：如果你有多个应该共享相同操作的应用程序，那么创建一个过程是将逻辑封装到一个单独的一种方法地点。当然，在许多情况下，这也可以在主叫方面完成。



对客户端不可知：无论客户端类型如何，它是否是客户端服务器应用程序，使用哪种编程语言等，程序总是一样的。



关于安全性，使用存储过程调用者不需要特权来对实际的数据库对象进行操作。调用者只需要对该过程具有执行权限。这取决于系统，这可以带来巨大的安全益处。

In my opinion there is quite a big difference between executing stored procedures versus executing individual statements. In both you should use parameterization so from a single statement point of view the difference is not so big.

However, consider a situation where you need to execute multiple statements in order to get the desired result. If the statements are located on the calling side, you need to execute them separately so each execution means a roundtrip to database and back. This can be time really consuming.

Another scenario: If you have multiple applications which should share the same operation creating a procedure is one way to encapsulate the logic into a single place. Of course in many situations this can also be done on the calling side.

Agnostic to the client: Regardless of the client type, is it a client server application or not, which programming language is used etc the procedure is always the same.

About security, with stored procedures the caller does not need privileges to the actual database objects to do the operation. The caller needs only an execute privilege to the procedure. Depending on the system this can be a huge security benefit.

这篇关于参数化查询和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！