blocks|key|1555875|text|我会从阅读这篇文章开始：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1555876|http://decipherinfosys.wordpress.com/2007/03/27/using-stored-procedures-vs-dynamic-sql-generated-by-orm/|offset|length|1555877|下面是两者之间的速度测试：|1555878|http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx|1555879|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|2W|0|0|0|0|1D|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|J|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|C]]|Q|$5|M|N|O|A|$P|I]]]]

I would start by reading this article:

<a href="http://decipherinfosys.wordpress.com/2007/03/27/using-stored-procedures-vs-dynamic-sql-generated-by-orm/" rel="nofollow noreferrer">http://decipherinfosys.wordpress.com/2007/03/27/using-stored-procedures-vs-dynamic-sql-generated-by-orm/</a>

Here is a speed test between the two:

<a href="http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx" rel="nofollow noreferrer">http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx</a>

blocks|key|1771050|text|第1轮-您可以启动分析器跟踪并比较执行时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1771051|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Round 1 - You can start a profiler trace and compare the execution times.

blocks|key|1555914|text|对于大多数人来说，说服他们的最好方法是“向他们展示证据”。在本例中，我将创建两个基本的测试用例来检索相同的数据集，然后计算使用存储过程与使用NHibernate所需的时间。一旦你有了结果，把它交给他们，大多数持怀疑态度的人应该向证据让步。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1555915|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For most people, the best way to convince them is to "show them the proof." In this case, I would create a couple basic test cases to retrieve the same set of data, and then time how long it takes using stored procedures versus NHibernate. Once you have the results, hand it over to them and most skeptical people should yield to the evidence.

blocks|key|1556214|text|对于Rob的回答，我只会补充几点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1556215|首先，确保测试用例中涉及的数据量与生产值相似。换句话说，如果您的查询通常针对具有数十万或数十万行的表，则创建这样的测试环境。|1556216|其次，除了使用nHibernate生成的查询和s‘’proc调用之外，其他条件都相同。希望您可以通过简单地交换一个提供者来执行测试。|1556217|最后，要认识到，通常不仅仅是存储过程与ORM之间存在更多的风险。考虑到这一点，测试应该考虑所有因素:执行时间、内存消耗、可伸缩性、调试能力等。|1556218|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

I would only add a couple things to Rob's answer: 

First, Make sure the amount of data involved in the test cases is similiar to production values. In other words if your queries are normally against tables with hundreds of thousands or rows, then create such a test environment. 

Second, make everything else equal except for the use of an nHibernate generated query and a s'proc call. Hopefully you can execute the test by simply swapping out a provider.

Finally, realize that there is usually a lot more at stake than just stored procedures vs. ORM. With that in mind the test should look at all of the factors: execution time, memory consumption, scalability, debugging ability, etc.

blocks|key|1771086|text|你自己做基准吧。编写一个执行一个采样存储过程几百次的测试床类，并运行相同次数的NHibernate代码。比较每种方法的平均执行时间和中值执行时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1771087|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Benchmark it yourself. Write a testbed class that executes a sampled stored procedure a few hundred times, and run the NHibernate code the same amount of times. Compare the average and median execution time of each method.

blocks|key|1556048|text|如果每次查询都是相同的，那么速度也是一样的。无论Sql来自何处，SQL+Server2005都会在批处理中的每个语句级别缓存查询计划。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1556049|长期的区别可能是存储过程对于DBA来说更容易管理和调优，而必须从分析器跟踪中收集的数百个不同的查询是一场噩梦。|1556050|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It is just as fast if the query is the same each time. Sql Server 2005 caches query plans at the level of each statement in a batch, regardless of where the SQL comes from.

The long-term difference might be that stored procedures are many, many times easier for a DBA to manage and tune, whereas hundreds of different queries that have to be gleaned from profiler traces are a nightmare.

blocks|key|1771188|text|这个争论我已经讨论过很多次了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1771189|我最终几乎总是抓取一个非常好的dba，并在分析器运行的情况下运行一个proc和一段代码，然后让dba显示结果是如此接近以至于可以忽略不计。|1771190|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I've had this argument many times over. 
Almost always I end up grabbing a really good dba, and running a proc and a piece of code with the profiler running, and get the dba to show that the results are so close its negligible.

blocks|key|1556146|text|测量它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1556147|真的，任何关于这个话题的讨论都可能是徒劳的，除非你对它进行了衡量。|1556148|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Measure it. 

Really, any discussion on this topic is probably futile until you've measured it.

blocks|key|1556171|text|对于他正在考虑的特定用例，他可能是正确的。对于一些可以任意调优的复杂SQL集，存储过程的执行速度可能会更快。然而，你从hibernate之类的东西中得到的东西是缓存。在实际应用程序的生命周期中，这可能会快得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1556172|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

He may be correct for the specific use case he is thinking of. A stored procedure will probably execute faster for some complex set of SQL, that can be arbitrarily tuned. However, something you get from things like hibernate is caching. This may prove much faster for the lifetime of your actual application.

blocks|key|1556255|text|额外的抽象层将导致它比对存储过程的纯调用慢。由于您在托管堆上有额外的分配，以及调用堆栈的额外推送和弹出，所以事实是，调用存储过程比让ORM构建查询更有效，无论ORM有多好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1556256|速度有多慢，如果它甚至可以衡量的话，是有争议的。这也是因为大多数ORM都有一个缓存机制来避免查询。|1556257|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The additional layer of abstraction will cause it to be slower than a pure call to a sproc. Just by the fact that you have additional allocations on the managed heap, and additional pushes and pops off the callstack, the truth of the matter is that it is more efficient to call a sproc over having an ORM build the query, regardless how good the ORM is.

How slow, if its even measurable, is debatable. This is also helped by the fact that most ORM's have a caching mechanism to avoid doing the query at all.

blocks|key|1771404|text|即使存储过程快了10%25+(可能不是)，您也可能想问问自己，它到底有多重要。归根结底，真正重要的是为系统编写和维护代码有多容易。如果您正在编写一个web应用程序，并且所有页面都在0.25秒内返回，那么使用存储过程节省的额外时间可以忽略不计。然而，使用像NHibernate这样的对象关系管理还有许多额外的优点，只使用存储过程很难复制这些优点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1771405|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Even if the stored procedure is 10% faster (it probably isn't), you may want to ask yourself how much it really matters. What really matters in the end, is how easy it is to write and maintain code for your system. If you are coding a web app, and your pages all return in 0.25 seconds, then the extra time saved by using stored procedures is negligible. However, there can be many added advantages of using an ORM like NHibernate, which would be extremely hard to duplicate using only stored procedures.

One of my co-workers claims that even though the execution path is cached, there is no way parameterized SQL generated from an ORM is as quick as a stored procedure. Any help with this stubborn developer?

Why is parameterized SQL generated by NHibernate just as fast as a stored procedure?

我的一个同事声称，即使执行路径被缓存，从ORM生成的参数化SQL也不可能像存储过程那样快。对这个顽固的开发者有什么帮助吗？

问为什么NHibernate生成的参数化SQL和存储过程一样快？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么NHibernate生成的参数化SQL和存储过程一样快？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么NHibernate生成的参数化SQL和存储过程一样快？
EN