StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>GUID vs.Sequential GUID</strong></p> <p><br/><br/> A typical pattern it's to use Guid as PK for tables, but, as referred in other discussions (see <a href="https://stackoverflow.com/questions/45399/advantages-and-disadvantages-of-guid-uuid-database-keys">Advantages and disadvantages of GUID / UUID database keys</a>) there are some performance issues. <br/><br/> This is a typical Guid sequence<br/> <br/></p> <p>f3818d69-2552-40b7-a403-01a6db4552f7<br/> 7ce31615-fafb-42c4-b317-40d21a6a3c60<br/> 94732fc7-768e-4cf2-9107-f0953f6795a5<br/> <br/><br/> Problems of this kind of data are:<<br/> - </p> <ul> <li>Wide distributions of values</li> <li>Almost randomically ones</li> <li>Index usage is very, very, very bad</li> <li>A lot of leaf moving </li> <li>Almost every PK need to be at least on a non clustered index</li> <li>Problem happens both on Oracle and SQL Server</li> </ul> <p><br/><br/> A possible solution is using Sequential Guid, that are generated as follows:<br/> <br/> cc6466f7-1066-11dd-acb6-005056c00008<br/> cc6466f8-1066-11dd-acb6-005056c00008<br/> cc6466f9-1066-11dd-acb6-005056c00008<br/> <br/><br/> </p> <p>How to generate them From C# code:<br/><br/></p> <pre><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; } </code></pre> <p><br/> Benefits</p> <ul> <li>Better usage of index</li> <li>Allow usage of clustered keys (to be verified in NLB scenarios)</li> <li>Less disk usage</li> <li>20-25% of performance increase at a minimum cost</li> </ul> <p><br/><br/> <strong>Real life measurement:</strong> Scenario:<br/></p> <ul> <li>Guid stored as UniqueIdentifier types on SQL Server</li> <li>Guid stored as CHAR(36) on Oracle</li> <li>Lot of insert operations, batched together in a single transaction</li> <li>From 1 to 100s of inserts depending on table</li> <li>Some tables > 10 millions rows</li> </ul> <p><br/><br/> Laboratory Test – SQL Server<br/> <br/>VS2008 test, 10 concurrent users, no think time, benchmark process with 600 inserts in batch for leaf table <br/><em>Standard Guid</em> <br/>Avg. Process duration: <strong>10.5</strong> sec <br/>Avg. Request for second: <strong>54.6</strong> <br/>Avg. Resp. Time: <strong>0.26</strong> <br/><br/><em>Sequential Guid</em> <br/>Avg. Process duration: <strong>4.6</strong> sec <br/>Avg. Request for second: <strong>87.1</strong> <br/>Avg. Resp. Time: <strong>0.12</strong> <br/><br/> <em>Results on Oracle</em> (sorry, different tool used for test) 1.327.613 insert on a table with a Guid PK<br/> <br/><em>Standard Guid</em>, <strong>0.02</strong> sec. elapsed time for each insert, <strong>2.861</strong> sec. of CPU time, total of <strong>31.049</strong> sec. elapsed <br/><br/><em>Sequential Guid</em>, <strong>0.00</strong> sec. elapsed time for each insert, <strong>1.142</strong> sec. of CPU time, total of <strong>3.667</strong> sec. elapsed <br/><br/>The DB file sequential read wait time passed from <strong>6.4</strong> millions wait events for <strong>62.415</strong> seconds to <strong>1.2</strong> million wait events for <strong>11.063</strong> seconds. <br/><br/> 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.<br/> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload