存储过程和 SQL Server 2008 性能的好处 [英] Benefits of stored procedures and SQL Server 2008 performance
问题描述
我正在编写一个数据库前端，它目前依赖于客户端 UI 和数据库之间的数据访问层.作为一个 SQL 菜鸟(因为这不是一个高安全性的应用程序)，我很高兴将 DAL 作为在存储过程中编写所有数据库逻辑的替代方法.
我现在关心的是速度.我知道 SQL Server 会编译存储过程并比随机查询更快地执行它们，但我找不到任何关于这实际上对性能有多大影响的信息.
我知道，如果我正在设计 SQL Server，我至少会考虑透明地缓存常见查询，以及根据需要动态存储和编译过程以适应常见查询.我想这将在很大程度上消除许多场景中对存储过程的需求(显然，除了封装数据库.)
但目前我的数据库非常小，所以我无法确定它的性能如何.我在这里走错路了吗?一旦数据库增长到数百万条记录并且我发现我的方法错误，我最终会痛苦地将所有逻辑迁移到存储过程吗?
在相关说明中，SELECT 的性能如何随表大小而变化?我似乎找不到任何关于此的资源，但决定如何布置我的数据库似乎至关重要.我是将所有条目都放入一个表中，然后依靠 WHERE 将它们隔离在逻辑组中，还是我将条目分组到单独的表中，因为从一百万行中选择 100 行非常慢?
如果您使用正确的参数化查询(使用像 @CustomerID 这样的参数，而不是将您的 SQL 语句连接在一起)，就不应该有存储过程和普通 SQL 语句之间有很大区别.普通查询和存储过程的执行计划都由 SQL Server 缓存，如果它们被频繁地重用，它们不会太快地从缓存中抛出.所以严格来说，从性能的角度来看，存储过程并没有真正给你带来太多好处.
存储过程很有用，因为它们可以提供另一层安全性 - 如果您的所有数据访问都通过存储过程进行，普通"用户通常甚至不需要对基表的读取权限.
至于表大小:如果你有适当的索引，一个简单的索引查找操作将需要在 SQL Server 中读取大约 3-6 页才能到达叶级 - 这对于多达几百万个数据页.重点是:正确编制索引，而且要做到正确并不总是那么容易和明显.
SQL Server 中最重要的一个方面是获得正确的簇索引，因为它定义了数据的物理顺序，而簇键是复制最多/冗余最多的数据块您的服务器 - 因此您希望尽可能提高效率.
查看 Kimberly Tripp 出色的博客文章 更多关于聚类键的注意事项，并研究她链接到的其他博客文章，以更好地了解什么是好的聚类键 - 这对于正确绝对至关重要！>
Kimberly - SQL Server 上的索引女王 - 也有大量关于如何选择好的非聚集索引以及何时少即是多(don也不要过度索引您的数据库 - 这可能比根本没有索引更糟糕)
I'm writing a database front-end that so far relies on a data access layer between the client UI and the database. As an SQL noob (and since this isn't a high-security application) I'm happy with the DAL as an alternative to writing all the database logic in stored procedures.
My concern at the moment is speed. I understand that SQL Server will compile stored procedures and execute them a lot faster than random queries, but I can't find any info on how much of an impact this actually has on performance.
I know that if I were designing SQL Server, I would at least consider transparently caching common queries, as well as dynamically storing and compiling procedures as needed to accomodate common queries. I imagine that this would largely eliminate the need for stored procedures in many scenarios (except for encapsulating the database, obviously.)
But at the moment my database is pretty small so I can't really tell how well it's performing. Am I on the wrong path here? Will I end up painfully migrating all the logic to stored procedures once the database grows to millions of records and I discover the error of my ways?
On a related note, how does the performance of SELECT scale with table size? I can't seem to find any resources on this, but it seems crucial to deciding how to lay out my database. Do I shove all my entries into one table, say, and rely on WHERE to isolate them in logical groups, or do I group entries into separate tables because SELECTing 100 out of a million rows is horribly slow?
If you use properly parametrized queries (with parameters like @CustomerID instead of concatenating together your SQL statement), there shouldn't be much difference between stored procedures and plain SQL statements. Both the execution plans for plain queries as well as for stored procedures are cached by SQL Server, and if they're re-used frequently, they won't be tossed out of the cached too quickly. So strictly from a performance perspective, stored procedures don't really give you much of a benefit.
Stored procedures can be beneficial since they can provide another layer of security - if all your data access goes via the stored procedures, the "regular" users don't typically need even read permissions on the base tables.
As for table size: if you have proper indexing in place, a simple Index Seek operation will take about 3-6 page reads in SQL Server to get to the leaf level - and that's for up to several million data pages. The point is : proper indexing and that's not always easy and obvious to get right.
One of the most important aspects in SQL Server is getting the clustering index right, since that defines the phsyical order of your data, and the clustering key is the most replicated / most redundant data piece in your server - so you want to make that as efficient as possible.
Check out Kimberly Tripp's outstanding blog post More Considerations For The Clustering Key and also study her other blog posts she links to to get a good understanding of what makes a good clustering key - this is absolutely crucial to get right!
Kimberly - the Queen of Indexing on SQL Server - also has a ton of great blog post on how to choose good nonclustered indices, and when less is more (don't over-index your database either - that could be even worse than no indexes at all)
这篇关于存储过程和 SQL Server 2008 性能的好处的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
