COMB指南的性能值 [英] Performance value of COMB guids
问题描述
Jimmy Nilsson在此处讨论了他的COMB指导概念. >.该概念在NHibernate等机构中很流行，因为它比常规GUID通常具有更大随机性的假定性能值高.
Jimmy Nilsson discusses his COMB guid concept here. This concept is popular in NHibernate, among other circles, for its supposed performance value over standard GUIDs which are typically far more random.
但是，在测试中似乎并非如此.我想念什么吗?
However, in testing, this does not appear to be the case. Am I missing something?
测试用例:
我有一个名为temp的表(不是临时表，只是一个名为"temp"的表)，其中有585,000行.我有一个名为代码"的新表，希望将所有585,000个代码值从临时表复制到代码表.我执行的测试SQL是:
I have a table called temp (not a temp table, just a table named "temp") with 585,000 rows in it. I have a new table called Codes, and wish to copy all 585,000 code values from the temp table to the codes table. The test SQL I executed was:
set statistics time on;
truncate table codes;
DBCC DBREINDEX ('codes', '', 90);
insert into codes (codeid, codevalue)
select newid(), codevalue from temp
truncate table codes;
DBCC DBREINDEX ('codes', '', 90);
insert into codes (codeid, codevalue)
select CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER), codevalue from temp
具有标准GUID值的性能:
Performance with standard GUID values:
SQL Server执行时间:CPU 时间= 17250毫秒，经过的时间= 15735 毫秒.
SQL Server Execution Times: CPU time = 17250 ms, elapsed time = 15735 ms.
(受影响的585000行)
(585000 row(s) affected)
具有COMB GUID值的性能:
Performance with COMB GUID values:
SQL Server执行时间:CPU 时间= 17500毫秒，经过的时间= 16419 毫秒.
SQL Server Execution Times: CPU time = 17500 ms, elapsed time = 16419 ms.
(受影响的585000行)
(585000 row(s) affected)
我想念什么? COMB GUID值导致了更长的时间，大概是因为附加了转换.我以为要点是通过使用后6个字节的日期对GUIDS进行半排序来减少插入时间，但是性能提升似乎不存在.
What am I missing? the COMB GUID values resulted in slightly longer times, presumably because of the additional conversions. I thought the point was to reduce the insert time by semi-ordering the GUIDS using the date for the last 6 bytes, but the performance gain appears non-existent.
推荐答案
我第二个观点是，仅当您在Guid列队上拥有索引(PK，FK或其他类型的索引，已聚集或未聚集)时，您才会看到差异，因为标准guid相对于newguid或comb guid的成本是由于每次执行插入都需要对索引数据重新排序的高成本.
I second that you'll see differences only when you have indexes (PK, FK or other kind of indexes, clustered or not clustered) on the Guid colume, because cost of standard guid versus newguid or comb guid is due to the high cost of re-ordering the index data every time an insert is performed.
请参见我的问题在其中，我用来自SQL Server和Oracle的一些实际数据来证实了这一点.
See my question in which I corroborate this with some real life data from both SQL Server and Oracle.
这篇关于COMB指南的性能值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
