Int PK内部连接Vs Guid PK内部加入SQL Server。执行计划 [英] Int PK inner join Vs Guid PK inner Join on SQL Server. Execution plan
问题描述
我刚刚对Int PK加入Vs Guid PK进行了一些测试。
表结构和数量如下：
性能使用EF4的CRUD操作在这两种情况下都非常相似。
有一个众所周知的声明，Int PK在连接时使用时具有更好的性能而不是字符串。
所以SQL Server执行计划与INNER JOINS完全不同
这是一个执行计划：
正如我所理解的那样执行从上面的计划Int加入具有更好的性能，因为它花费更少的资源用于聚簇索引扫描，它有两种方式，我是对吗？
可能有人可能会更详细地解释这个执行计划？
这个例子足以显示Int PK在联接中有更好的表现？
我不完全确定我明白你正在努力实现或找到什么从这个测试出来，但是这里有一些随机的内容，在我阅读你的问题时弹出我的想法...
1）一个现实的生活用例，你可能不会一起加入两个整个表，但是在其他列等上会有过滤器，减少要在一个表中或两个表中加入的记录。这将影响最合适/最有效的连接算法类型。
上面的计划是将两个表连接在一起的结果，但是如果要过滤一个或另一列上的两个表，然后优化器可能会使用完全不同的连接类型。
2）加入GUID列时，最适合使用哪种类型的连接取决于GUID的生成方式。如果你加入了很多完全随机的guid（例如使用SQL Server的NewID（）或CLR Guid.NewGuid（）生成），那么散列连接可能是最好的选择。然而，如果你正在加入一个较小的一连串（newsequentialid（）/ UuidCreateSequential（）），或者甚至是相同的guids，那么循环连接通常可以是最有效的选择。
优化器使用索引统计信息来确定要使用的联接类型，但有时候对于具有许多guid连接的复杂查询，可能需要使用优化器提示强制连接类型。
简单来说，如果你想做的是决定是否使用GUID或INT PK，那么更实际的测试是一个更好的选择。创建与您的用例匹配的表，填充大量有用的实例样本数据，然后执行您想要的一些查询类型，您将会做下去。将两个虚拟表的全部内容连接在一起并不能说明使用Guid键可以看到的I / O影响的任何内容，或者涉及int vs guid键的其他查询的执行计划的外观。
如果使用Guid键，请考虑生成它们的不同选项，并记住，如果您加入了大量记录，使用顺序guids通常是避免过多页面读取的好方法...
I just did some testing for Int PK join Vs Guid PK.
Tables structure and number of records looking like that:
Performance of CRUD operations using EF4 are pretty similar in both cases.
There is well known statement that Int PK has better performance rather than strings when used in joins. So SQL server execution plan with INNER JOINS are completely different
Here is an execution plan:
As i understand according with execution plan from above Int join has better performance because it is taking less resources for Clustered index scan and it is go in two ways, am i right?
May be some one may explain this execution plan in more details?
Is this example is enough to show that Int PK has better performance in joins?
I'm not totally sure I understand what you are trying to achieve or find out from this test, but here are a few random throughts that popped into my mind as I read your question...
1) In a real life use case you are probably not going to join two entire tables together, but there will be filters on other columns etc, reducing the records to be joined in one or both tables. This will affect what type of join algorithm is most suitable/most effective.
The plans above are the results of joining two tables together, but if you were to filter one or both of the tables on some other column then the optimizer might go for a completely different join type.
2) Which type of join is best when joining GUID columns depend a lot on how the guids are generated. If you're joining a lot of guids that are completely random (e.g. generated with SQL Server's NewID() or CLR Guid.NewGuid()) then a hash join is probably the best choice. If however you're joining a smaller set of sequential (newsequentialid() / UuidCreateSequential()), or even identical guids, then a loop join can often be the most efficient choice.
The optimizer uses index statistics to determine what type of join to use, but sometimes for complex queries with many guid joins it can be necessary to force join type with optimizer hints.
In short, if what you're trying to do is decide whether you should use GUID or INT PKs then a more real-world test is a better choice. Create tables matching your use case, populate them with an ample amount of somewhat realistic sample data and do some of the types of queries you envision that you will be doing down the line. Joining the entire contents of two dummy tables together doesn't really tell anything about the I/O impact you could see from using Guid keys, or what the execution plan will look like for other queries involving int vs guid keys.
If using Guid keys, consider the different options for generating them and keep in mind that using sequential guids is often a good way to avoid excessive page reads if you're joining a lot of records...
这篇关于Int PK内部连接Vs Guid PK内部加入SQL Server。执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
