SQL Server In-Memory OLTP
In-Memory OLTP comes with Microsoft SQL Server 2014 and can significantly improve OLTP database application performance. It is a memory-optimized database engine integrated into the standard SQL Server engine. This system provides memory-optimized tables which are fully transactional and are accessed using class Translact-SQL instructions.
In-Memory Tables comes with some limitations. We won’t enumerate all but only those which are related to CodeFluent Entities:
- Foreign keys aren’t supported
- RowVersion and Timestamp columns aren’t supported: http://msdn.microsoft.com/en-us/library/dn133179.aspx
- Default constraints aren’t supported
- Some Transact-SQL constructs aren’t supported: http://msdn.microsoft.com/en-us/library/dn246937.aspx
Let’s handle those four points!
There are two options:
- Don’t create relation 😦
- Create relation without foreign key 🙂
The second solution requires the usage of an Aspect. Fortunately we already wrote it a few time ago: http://www.softfluent.com/forums/codefluent-entities/how-to-disable-creating-foreign-key-by-sql-producer-
Even if foreign keys do not exist anymore, CodeFluent Entities still generates LoadBy_Relation methods so you won’t see any difference in your code. 🙂
RowVersion is not supported by In Memory tables so let’s remove it. We have to set “Concurrency Mode” to “None”:
Default constraints used by tracking columns (creation time & last write time) are not supported. Here we have two options:
- Remove default constraints 😦
- Move them into the Save stored procedure 🙂
The first option is available in the Property Grid at project or entity level by removing the tracking time columns:
The second option can be done with an Aspect as you can see in the full example (see below). The edited INSERT statement looks like:
INSERT INTO [Customer] ( [Customer].[Customer_Id], [Customer].[Customer_Name], [Customer].[Customer_ContactSource_Id], [Customer].[_trackCreationUser], [Customer].[_trackLastWriteUser], [Customer].[_trackLastWriteTime]) VALUES ( @Customer_Id, @Customer_Name, @Customer_ContactSource_Id, @_trackLastWriteUser, @_trackLastWriteUser, (GETDATE())) -- Default Value
By default the SQL Server Producer surround the procedure code with a transaction. This transaction isn’t supported when using In Memory Table. The following exception is thrown when calling the stored procedure:
Unhandled Exception: System.Data.SqlClient.SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).
To remove it, we have to configure the SQL Server to not produce it:
Migrate the table
After those small changes, we can migrate the table to an In Memory table:
We can now use the In Memory table from the application:
Customer customer = new Customer(); customer.Name = "John Doe"; customer.Save();
All the previous steps are automated by an aspect. All you have to do is include the aspect and set “enabled” on tables:
The full code sample including the aspect is available on our GitHub repository.
The R&D Team