Performance Comparison of using the self-increment field and guid field in the database as the primary key (Supplement) -- convert

After I published the article "Performance Comparison of Self-increment fields and guid field primary keys used in the Database", I got a lot of comments from my friends in the blog Park, we have also proposed some improvement methods for my testing methods. What surprised me was that a garden friend proposed that the test sequence of guid and ID should be reversed and the results should be viewed. Let's test it again today. You are welcome to propose a better test solution.

1. Test Environment

Operating System: Windows Server 2003 R2 Enterprise Edition Service Pack 2

Database: ms SQL 2008 Express

CPU: Intel (r) Pentium (r) 4 CPU 3.40 GHz

Memory: DDR ⅱ 667 1g

Hard Disk: WD 80g

2. Database scripts

CREATE TABLE [dbo].[Table_Guid](    [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table_Guid_Guid]  DEFAULT (newid()),    [Value] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Table_Guid] PRIMARY KEY CLUSTERED (    [Guid] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO     CREATE TABLE [dbo].[Table_Id](    [Id] [int] IDENTITY(1,1) NOT NULL,    [Value] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Table_Id] PRIMARY KEY CLUSTERED (    [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GO

First, let's take a look at the test code:

Code

To eliminate the above concerns, only one test method is used each time (no code is commented every time ).

1. Write test of auto-increment ID.

 

Write test of 1.2.guid.

2. 1. Test the function of reading auto-incremental IDs to the datatable.

2.2.guid read to datatable Test

. Statistics on the total number of auto-incremental IDS

3.2.guid statistics

3,000th. Statistics on the total number of auto-increment IDS (manually locate the ID of data records and then query)

4.2.guid statistics on the total number of data records (manually locate the ID of the first 3,000th data records and then query)

The above tests are all tests on my computer. Each test result is tested several times before obtaining a set of relatively average results.

Supplement (I don't want to summarize it. In fact, some practical applications have been summarized in the previous article. Please try again ):
1. The GUID as the primary key in the test result is better than the auto-increment ID in the above test. Inner join is not tested yet.
2. Use that type as the primary key based on the specific needs. Repeated self-increment fields may occur during database migration or data import, which is undoubtedly a nightmare, and The GUID format is undoubtedly the first choice. However, using the guid format is complicated and complicated for programs. After all, the guid format is hard to remember.
3. The Automatically increasing IDs use the Int or bigint type. They occupy 4 bytes and 8 bytes respectively, and The GUID is the uniqueidentifier type, which occupies 16 bytes. In terms of storage space, automatically increasing IDs saves more space.
4. If you want to build a distributed database, this self-increment field will be faulty. In distributed databases, tables of the same name in different databases may need to be synchronized. The auto-increment value of a database table is likely to be the same as that of another database table.

I personally prefer to use guid as the primary key because it is unique, and it is unique During task operations. The Database Import and Export will not duplicate the primary key.
My personal questions:

1. I am using an article written by Windows Live writer. To facilitate code pasting, I use the from Visual Studio plug-in to paste the code, but if the Code contains Chinese characters, such as comments, after pasting, each Chinese character is followed by an additional "? ", I don't know how to solve this problem. I still cannot solve the problem by setting the encoding method.

2. In Windows Live writer, how to set the download link for the code (After packaging and uploading.

In addition, I would like to recommend an article to my friends who like databases: various stages of SQL Server Query Processing.
For more information about auto-increment IDs and guids, see the performance comparison between self-increment fields and guid field primary keys in the database.

Test code