Create Table – Disk vs In-Memory Optimized
I had an interesting e-mail discussion related to the performance of create table. The test being executed was a tight loop of create table statements. The assumption put forth was in-memory optimized table creation was slower and they expected it to be faster. We are not talking about the performance of inserts, updates, deletes and selects but instead looking at the time it takes to perform create table.
The answer is that the creation of in-memory optimized tables might be slower than disk based table creation. Let’s take a high level look at the create table activities.
|Action||Disk Based||In-Memory Optimized|
|Build Create Plan||Y||Y|
|Update System Table Info||Y||Y|
|Create Access DLL Code||N||Y|
|Compile Access DLL||N||Y|
Note: An in-memory, optimized table creates a native DLL (placed in the XTP directory) allowing common select, update, delete and insert activities. These are additional steps that a disk based table does not perform. The creation of the XTP, common access DLL requires SQL Server to generate C code, invoke the C compiler and save the DLL into the XTP directory.
While your first thought might be that in-memory optimized is faster, if you take a deep breath and step back you can see the difference. You give up a bit of time to create the native access DLL, which enables the performance improvements for select, update, delete and insert over a disk based table. While you might be able to create the disk based table slightly faster, each time you access the table you have to generate or lookup a T-SQL plan and execute it. The XTP DLL was optimally built at create table time to allow you fast access to your data.
Bob Dorr - Principal Software Engineer SQL Server