Performance Test of using guid as the primary key and INT as the primary key of the database

Source: Internet
Author: User
Tags dell laptop
  1. In a large application, if you need more than two sets of databases (such as SQL Server and Oracle), Can You encapsulate all the required SQL queries in the stored procedure, in this way, you only need a set of access code. Is there a better solution to this problem?
  2. It is easy to directly use guid as the primary key when setting the primary key of a database (multiple databases are supported at the same time). But there are not many factors that affect the performance during query. Is there any better solution?

The above two questions are briefly answered by Microsoft engineers due to the time. The first question should be taken into consideration for specific applications. However, the second question must have a certain performance impact, but the impact is not big. With this problem, I did this small experiment.

Note: If you have better suggestions, we will discuss them!

Test environment:

  • Dell laptop 1.5 GB
  • Win XP Professional
  • 512 MB DDR RAM
  • SQL Server 2000 Personal Edition

Test method:

  • Create a database [test_guid] with 10 fields. Use guid as the primary key and other common field types to simulate the actual usage. The SQL code for table creation is as follows:

    Create Table [DBO]. [test_guid] (
    [Guid] [varchar] (50) Collate chinese_prc_ci_as not null,
    [Test1] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test2] [datetime] Null,
    [Test3] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test4] [varchar] (100) Collate chinese_prc_ci_as null,
    [Test5] [varchar] (100) Collate chinese_prc_ci_as null,
    [Test6] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test7] [text] collate chinese_prc_ci_as null,
    [Test8] [int] Null,
    [Test9] [int] Null
    ) On [primary] textimage_on [primary]
    Go

    Alter table [DBO]. [test_guid] With nocheck add
    Constraint [pk_test_guid] primary key clustered
    (
    [Guid]
    ) On [primary]
    Go

  • Create a database [test_iidd] with 10 fields, use iidd as the primary key, and other common field types to simulate the actual usage. The SQL code for table creation is as follows:

    Create Table [DBO]. [test_iidd] (
    [Iidd] [numeric] (9) Identity (1, 1) not null,
    [Test1] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test2] [datetime] Null,
    [Test3] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test4] [varchar] (100) Collate chinese_prc_ci_as null,
    [Test5] [varchar] (100) Collate chinese_prc_ci_as null,
    [Test6] [varchar] (50) Collate chinese_prc_ci_as null,
    [Test7] [text] collate chinese_prc_ci_as null,
    [Test8] [int] Null,
    [Test9] [int] Null
    ) On [primary] textimage_on [primary]
    Go

    Alter table [DBO]. [test_iidd] With nocheck add
    Constraint [pk_test_iidd] primary key clustered
    (
    [Iidd]
    ) On [primary]
    Go

  • As you can see, the first table uses a globally unique identifier (guid) as the primary key, while the second table uses a normal numeric (similar to int type) data type as the primary key, here is a brief introduction to guid:
    Guid, a globally unique identifier, is often used in the COM component identifier. Because it is almost impossible to generate duplicate two values, it is often used in various fields. The specific values are as follows: as shown in the A89C9547-032B-4860-ABB5-6EAEAVE934D5, you must have seen a similar string, ^ _ ^, using the newid () function in SQL Server2000 to get a unique guid
  • Run the following two SQL statements to insert 0.1 million statements to the two tables respectively. I am concerned about the effect of large data volumes, so don't blame me for choosing 0.1 million data records when I start.

    Declare @ num int
    Set @ num = 0
    While (@ num <100000)
    Begin

    Insert into test_guid
    Values (
    Newid (),
    'X22222222222222222222222222 ',
    Getdate (),
    'Aaaaaaaaaaaaaaaaaaa ',
    'Bbbbbbbbbbbbbbbbbbbbbb ',
    'Cccccccccccccccccccccccccccccccc ',
    'Ddddddddddddddd ',
    '479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    ',
    '1 ',
    '0'
    )

    Set @ num = @ num + 1
    End

    Declare @ num int
    Set @ num = 0
    While (@ num <100000)
    Begin

    Insert into test_iidd
    Values (
    'X22222222222222222222222222 ',
    Getdate (),
    'Aaaaaaaaaaaaaaaaaaa ',
    'Bbbbbbbbbbbbbbbbbbbbbb ',
    'Cccccccccccccccccccccccccccccccc ',
    'Ddddddddddddddd ',
    '479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    479c8aad-3040-4fc5-b53a-d6af085ad38a
    ',
    '1 ',
    '0'
    )

    Set @ num = @ num + 1
    End

  • Start the test. The test code and result are as follows:
    # Test 1 (guid)

    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select * From test_guid
    Where
    Guid = 'a89c9547-032b-4860-abb5-6eaea0e934d5 'or
    Guid = 'fffa8619-BC9F-4B76-ACE8-B3324105BBDE 'or
    Guid = 'fffc26d5-6ecf-479d-838d-0d3e23ac7d2d 'or
    Guid = 'fff9fa53-E115-450A-A52D-B0AET36FF539 'or
    Guid = 'a89c9547-032b-4860-abb5-6eaeave934d5 'or
    Guid = 'fff90a0b-CB5B-446F-81FC-CFA661D03CF8 'or
    Guid = 'fff85f4a-4554-491f-9d1a-05c8ba3c1266 'or
    Guid = 'ffff354a-ED3E-4C3A-A033-3406F229EB34'
    Order by guid DESC

    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------

    0 seconds, 0 milliseconds, sometimes 10 milliseconds
    # Test 2 (iidd)

    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select * From test_iidd
    Where
    Iidd = '1' or
    Iidd = '2' or
    Iidd = '000000' or
    Iidd = '000000' or
    Iidd = '000000' or
    Iidd = '3' or
    Iidd = '8' or
    Iidd = '000000'
    Order by iidd DESC

    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------

    0 seconds, 0 milliseconds, sometimes 10 milliseconds
  • We can see that the efficiency of normal select queries is not significant in the case of 0.1 million data records.
    # Test 3 (guid)
    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select count (*) from test_guid
    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------
    29 seconds, 28793 milliseconds, bad results!
    # Test 4 (iidd)
    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select count (*) from test_iidd
    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------
    The first running is 3 seconds, the second running is 1 second, and the third running is 0 seconds, 50 milliseconds, my God!
  • How is this good? guids suffer a big loss when there is no WHERE clause for aggregation operations
    # Test 5 (guid)
    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select count (*) from test_guid
    Where
    Test2> '2017-06-03 21:05:33. 123'
    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------
    29 seconds, 29093 milliseconds, although the query only found more than 200 pieces of data, the speed has not changed!
    # Test 6 (iidd)
    --------------------
    Declare @ times datetime
    Set @ times = getdate ()
    --------------------
    Select count (*) from test_iidd
    Where
    Test2> '2017-06-03 21:05:33. 123'
    ---------------------
    Select datediff (second, @ times, getdate () as Second, datediff (MS, @ times, getdate () as millisecond
    ---------------------
    The first running is 2 seconds, the second running is 0 seconds, and the second running is 160 milliseconds, which is a little slower than that without where.
  • As shown in the results, the results are not ideal.
    # Test (guid)
    Add the Test2 column (datetime) of the table test_guid as the index column.

    Run Test 3: 0 seconds, 50 milliseconds...

    Run Test 5: 0 seconds, 0 milliseconds. It is very obvious.

    # Test 8 (iidd)
    Add the Test2 column (datetime) of the table test_iidd as the index column.

    Run Test 4: 0 s, 40 ms

    Run Test 6: 0 s, 40 ms

  • Test 7 and Test 8 have different return values, which may cause minor differences. This can be ignored (because I tested that the difference is very small when the return values are the same)
  • It can be seen that adding a time-type or Int-type index to the table with guid as the primary key can compensate for the performance loss caused by using guid as the primary key.

Summary:

Due to the time, this test is one-sided and superficial. We also hope that we can supplement and improve the deficiencies and omissions, after this test, I think I will perform more tests on performance. Please do it again.

Full access to SQL syntax (1)

Full access to SQL syntax (2)

Full access to SQL syntax (3)

Full access to SQL syntax (4)

Full access to SQL syntax (5)

Full access to SQL syntax (6)

Full access to SQL syntax (7)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.