What are the performance improvement of Sequential Guid over standard Guid? [closed]

by

GUID vs.Sequential GUID

A typical pattern it’s to use Guid as PK for tables, but, as referred in other discussions (see Advantages and disadvantages of GUID / UUID database keys)
there are some performance issues.

This is a typical Guid sequence

f3818d69-2552-40b7-a403-01a6db4552f7
7ce31615-fafb-42c4-b317-40d21a6a3c60
94732fc7-768e-4cf2-9107-f0953f6795a5

Problems of this kind of data are:<
–

  • Wide distributions of values
  • Almost randomically ones
  • Index usage is very, very, very bad
  • A lot of leaf moving
  • Almost every PK need to be at least
    on a non clustered index
  • Problem happens both on Oracle and
    SQL Server

A possible solution is using Sequential Guid, that are generated as follows:

cc6466f7-1066-11dd-acb6-005056c00008
cc6466f8-1066-11dd-acb6-005056c00008
cc6466f9-1066-11dd-acb6-005056c00008

How to generate them From C# code:

[DllImport("rpcrt4.dll", SetLastError = true)]
static extern int UuidCreateSequential(out Guid guid);

public static Guid SequentialGuid()
{
    const int RPC_S_OK = 0;
    Guid g;
    if (UuidCreateSequential(out g) != RPC_S_OK)
        return Guid.NewGuid();
    else
        return g;
}

Benefits

  • Better usage of index
  • Allow usage of clustered keys (to be
    verified in NLB scenarios)
  • Less disk usage
  • 20-25% of performance increase at a
    minimum cost

Real life measurement:
Scenario:

  • Guid stored as UniqueIdentifier
    types on SQL Server
  • Guid stored as CHAR(36) on Oracle
  • Lot of insert operations, batched
    together in a single transaction
  • From 1 to 100s of inserts depending
    on table
  • Some tables > 10 millions rows

Laboratory Test – SQL Server

VS2008 test, 10 concurrent users, no think time, benchmark process with 600 inserts in batch for leaf table

Standard Guid

Avg. Process duration: 10.5 sec

Avg. Request for second: 54.6

Avg. Resp. Time: 0.26

Sequential Guid

Avg. Process duration: 4.6 sec

Avg. Request for second: 87.1

Avg. Resp. Time: 0.12

Results on Oracle (sorry, different tool used for test) 1.327.613 insert on a table with a Guid PK

Standard Guid, 0.02 sec. elapsed time for each insert, 2.861 sec. of CPU time, total of 31.049 sec. elapsed

Sequential Guid, 0.00 sec. elapsed time for each insert, 1.142 sec. of CPU time, total of 3.667 sec. elapsed

The DB file sequential read wait time passed from 6.4 millions wait events for 62.415 seconds to 1.2 million wait events for 11.063 seconds.

It’s important to see that all the sequential guid can be guessed, so it’s not a good idea to use them if security is a concern, still using standard guid.
To make it short… if you use Guid as PK use sequential guid every time they are not passed back and forward from a UI, they will speed up operation and do not cost anything to implement.

More Related Contents:

  1. GUID vs INT IDENTITY [duplicate]
  2. What’s the Hi/Lo algorithm?
  3. Can I have multiple primary keys in a single table?
  4. Surrogate vs. natural/business keys [closed]
  5. Advantages and disadvantages of GUID / UUID database keys
  6. How to retrieve the last autoincremented ID from a SQLite table?
  7. Can we update primary key values of a table?
  8. What are the design criteria for primary keys?
  9. Oracle (ORA-02270) : no matching unique or primary key for this column-list error
  10. Is it fine to have foreign key as primary key?
  11. Deciding between an artificial primary key and a natural key for a Products table
  12. Should a database table always have primary keys?
  13. Is storing a delimited list in a database column really that bad?
  14. Best way to store password in database [closed]
  15. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  16. How are super- and subtype relationships in ER diagrams represented as tables?
  17. What’s the difference between a catalog and a schema in a relational database?
  18. Difference between scaling horizontally and vertically for databases [closed]
  19. How to import load a .sql or .csv file into SQLite?
  20. Spring Security 3 database authentication with Hibernate
  21. Why use a 1-to-1 relationship in database design?
  22. What is the optimal length for an email address in a database?
  23. Which embedded database to use in a Delphi application?
  24. Non-Relational Database Design [closed]
  25. There is already an object named in the database
  26. How to store images in your filesystem
  27. Limit an sqlite Table’s Maximum Number of Rows
  28. How do I automatically update a timestamp in PostgreSQL
  29. Maximum number of rows in a sqlite table
  30. Mysql service won’t start, InnoDB init function error

Leave a Comment