I developed the following test cases:
- A disk-based table with traditional stored procedures for DML.
- An In-Memory table with traditional stored procedures for DML.
- An In-Memory table with natively compiled procedures for DML.
I was interested in comparing performance of traditional stored procedures and natively compiled procedures, because one restriction of a natively compiled procedure is that any tables referenced must be In-Memory. While single-row, solitary modifications may be common in some systems, I often see modifications occurring within a larger stored procedure with multiple statements (SELECT and DML) accessing one or more tables. The In-Memory OLTP documentation strongly recommends using natively compiled procedures to get the most benefit in terms of performance. I wanted to understand how much it improved performance.
Read on for the results.