Tags

, , , ,

We give three scenarios: inserts of table rows with natural keys (two integers plus a datetime); inserts of table rows with GUIDs as the keys; and archiving which can be thought of as a SQL SELECT of many rows, writing the data to a text file, deleting the rows, and then inserting corresponding rows in a table stored on a disk. We show results using a Microsoft SQL Server 2014 database.

Timings are in seconds (3600 = 1 hour) for approximately 16 million rows.

Insert natural keys 1 thread 16 threads 32 threads
HDD 7689 3479 2174
SSD 3632 1543 969
Optane 1316 583 373
Insert GUID keys 1 thread 16 threads 32 threads
HDD 9842 7342 5391
SSD 4744 2501 1496
Optane 2263 1580 1051
Archive 1 thread 16 threads 32 threads
HDD 14117 7397 5015
SSD 6376 4540 3096
Optane 2972 1833 1201

We would summarize as follows: the Optane was roughly FOUR to SIX times faster than a very good hard disk and ALMOST THREE times faster than a modern solid state drive.

 
Everyone should obtain an Optane.

 
We note that GUIDs should not be a designer’s first choice for a key and that 32 threads are very likely to be an over-subscription for many machines, so the measures using 16 threads are probably going to be more commonly encountered in practice.

Advertisements