计划使用动态SQL，但...... [英] Planning on going with dynamic SQL, but...
问题描述
我已经阅读了一些有关存储过程与动态sql辩论的帖子。我为自己运行了几次性能测试，看起来很干净。
鉴于此，我倾向于动态sql，主要是因为它会意味着一个
更少的地方有东西。
但是，在我们走这条路之前我们想问一个问题：
有没有令人信服的理由说明为什么我们不应该放弃所有存储的
过程并在我们的业务中将SQL写入我们的函数内
层（基本上是我们的数据访问层）？
或者，这些天它只是偏好吗？
我倾向于触发，但我有承认，如果我们所有调用它们的函数都没有
跟上所有这些函数会很好。
谢谢，
Ron
" Ronald S. Cook" < rc *** @ westinis.com写信息
新闻：或************* @ TK2MSFTNGP02.phx.gbl ......
< blockquote class =post_quotes>
有没有令人信服的理由说明为什么我们不应该放弃所有存储的
过程，只需将SQL写入我们业务中的函数内部即可/>
层（基本上是我们的数据访问层）？
两个主要原因：
1）存储过程是预编译的，所以它们（几乎总是）执行更多
高效（即更快）比动态SQL
2）
http：// www .google.co.uk / search？sourc ... = SQL +注入
-
http://www.markrae.net
6月13日，7：01下午，罗纳德·库克 < r ... @ westinis.comwrote：
我已经阅读了一些关于存储过程与动态sql辩论的帖子。我为自己运行了几次性能测试，看起来很干净。
鉴于此，我倾向于动态sql，主要是因为它会意味着一个
更少的地方有东西。
但是，在我们走这条路之前我们想问一个问题：
有没有令人信服的理由说明为什么我们不应该放弃所有存储的
过程并在我们的业务中将SQL写入我们的函数内
层（基本上是我们的数据访问层）？
或者，这些天它只是偏好吗？
我倾向于触发，但我有承认，如果我们所有调用它们的函数都没有
跟上所有这些函数会很好。
谢谢，
Ron
Ron：
除Mark的评论外，存储过程通常更安全
就SQL注入攻击等而言。一个额外的
评论：我使用存储过程作为低级数据访问/数据写入
代理商并拥有我所有业务逻辑的所有业务
逻辑层我正在开发中。我知道，这些东西都有优点/缺点和神圣战争，但我发现这个经验法则对
年有用。
如果您正在使用SQL Server开发，我在
过去使用的一个有用的工具是SPInvoke。它将从SQL Server中读取SP并为您生成一个基于对象的数据访问层，支持同步和异步
调用存储过程 - 它的工作方式类似于一个魅力，已经节省了
我数百小时的开发。
祝你好运，
jpuopolo
有点OT，真的，但是...
我自己也是SP粉丝，但要反击现有的帖子：
*在许多情况下，在体面上RDBMS，性能将非常可比
;重新使用查询计划比以前更好，
特别是参数化命令。 SP可能会更快，但通常不会像你想象的那样多[见最后一点]
*理想情况下，命令你写将参数化;因此，它不会给你任何额外的注射保护，除非你是
严重写你的非SP命令
附加要点：
* SP往往是供应商特定的（可能甚至不存在于你的
RDBMS上）;如果供应商可移植性是一个问题，那么ORM就像NHibernate或
（稍后）D-LINQ可能是有意义的;这几乎总是原始的
（参数化）命令
* SP允许你更精细的安全控制;如果你没有CREATE / SELECT / INSERT / UPDATE / DELETE权限，很难破解数据库
，但是
只能执行现有的（已批准的）SP
*您可以在不重新编译客户端的情况下更改SP;特别是
如果客户端具有不同的架构（java，.Net，gupta等等，也可能是一些报告工具）也可以调用相同的SP
* SP可以像合同一样，在技术之间创建一个合理的连接点
;不幸的是，网格/列通常不是合同的一部分（只是名称和参数）所以这是一个弱的
比喻
*有时，怪异的表现恰好发生。我已经看到大量的b / b
性能变化只是在移动一个不起眼的，无害的[即从SP到第二个SP立即运行
，然后调用EXEC。
是的，我理解很多关于重新编译（DDL / DML交错，KEEP
PLAN等） - 这不是那么简单。使用原始SQL很难做到这一点
没有像sp_ExecuteSQL这样的混乱黑客攻击
进攻复杂;我目前的例子是一些temporal
数据库。我一直在做的工作。在这种情况下，你可以编写你的
SP（无论你想要的）脚本并调用1行命令而不是发送
一个巨大的命令;这是我的第一点的主要反点：
有时候，尺寸确实很重要。
Marc
I''ve read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.
Given that, I''m leaning toward dynamic sql mostly because it would mean one
fewer place to have things.
But, before we go that route we wanted to ask the question:
Is there any compelling reason why we shouldn''t abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
Or, is it just preference these days?
I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
Thanks,
Ron
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:Or*************@TK2MSFTNGP02.phx.gbl...
Is there any compelling reason why we shouldn''t abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?Two main reasons:
1) stored procedures are pre-compiled, so they (almost always) execute more
efficiently (i.e. faster) than dynamic SQL
2)
http://www.google.co.uk/search?sourc...=SQL+Injection
--
http://www.markrae.net
On Jun 13, 7:01 pm, "Ronald S. Cook" <r...@westinis.comwrote:I''ve read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.
Given that, I''m leaning toward dynamic sql mostly because it would mean one
fewer place to have things.
But, before we go that route we wanted to ask the question:
Is there any compelling reason why we shouldn''t abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
Or, is it just preference these days?
I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
Thanks,
RonRon:
In addition to Mark''s comment, stored procedures are generally safer
in terms of SQL injection attacks and the like. One additional
comment: I use stored procedures as low-level data access/data writing
agents and have all of my core business logic in whatever business
logic layer I''m developing. There are pros/cons and holy wars over
this stuff, I know, but I''ve found this rule of thumb useful over the
years.
If you are developing on SQL Server, one helpful tool I''ve used in the
past is SPInvoke. It will read the SPs from SQL Server and generate an
object-based data access layer for you, that support sync and async
calls into the stored procedures - it works like a charm and has saved
me hundreds of hours of development.
Good luck,
jpuopolo
A bit OT, really, but...
I''m an SP fan myself, but to counter the existing posts:
* in many cases, on "decent" RDBMS, the performance will be very
comparable; re-use of query plans is better than it used to be,
especially with parameterised commands. SPs might be faster, but often
not by as much as you''d think [see also last point]
* ideally, and command you write would be parameterised; as such, it
doesn''t give you any additional injection protection unless you are
writing your non-SP commands badly
Additional points:
* SPs tend to be vendor specific (perhaps not even existing on your
RDBMS); if vendor portability is an issue, then ORM like NHibernate or
(later) D-LINQ might be of interest; this is almost always raw
(parameterised) commands
* SPs allow you finer security control; it is hard to hack a database
if you don''t have CREATE/SELECT/INSERT/UPDATE/DELETE permissions, but
can only EXEC existing (approved) SPs
* You can change the SPs without recompiling the client; especially
useful if clients with different architectures (java, .Net, gupta, etc
- perhaps a few reporting tools too) call the same SPs
* SPs can act kinda like a contract, creating a reasonable join-point
between technologies; unfortunately, the grids/columns aren''t normally
part of that contract (just the name and params) so this is a weak
metaphor
* Sometimes, freaky performance just happens. I''ve seen massive
performance changes just be moving an obscure, inoffensive [i.e. runs
instantly on its own] line from an SP to a second SP and calling EXEC.
And yes, I understand lots about recompiles (DDL/DML interleave, KEEP
PLAN, etc) - it wasn''t that simple. Hard to do that using raw SQL
without messy hacks like sp_ExecuteSQL
* Sometimes, the SQL to do something that looks simple is just
offensively complex; my current example here is some "temporal
database" work I have been doing. In which case, you can script your
SPs (however you want) and invoke a 1-line command instead of sending
a huge command down; this is the main counter-point to my first point:
sometimes, size does matter.
Marc
这篇关于计划使用动态SQL，但......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
