Updating multiple tables in a ms access database
In the description of the sample we discuss the tradeoffs that were made in migrating the tables to In-Memory OLTP to account for the features that are not (yet) supported for memory-optimized tables.
The documentation of this sample is structured as follows: RESTORE DATABASE [Adventure Works2016CTP3] FROM DISK = N' C:\temp\Adventure Works2016CTP3.bak' WITH FILE = 1, MOVE N' Adventure Works2016_Data' TO N'h:\DATA\Adventure Works2016CTP3_Data.mdf', MOVE N' Adventure Works2016_Log' TO N'i:\DATA\Adventure Works2016CTP3_log.ldf', MOVE N' Adventure Works2016CTP3_mod' TO N'h:\data\Adventure Works2016CTP3_mod' GO The sample creates new tables for products and sales orders, based on existing tables in Adventure Works.
However, one must consider that they do not support range scans, ordered scans, or search on leading index key columns.
Therefore, care needs to be taken when using these indexes.
Concretely, the In-Memory OLTP sample adds the following objects to Adventure Works: Sales.
The workload relies on inserts and point lookups in the tables Sales. Sales Order Detail_inmem, and it also relies on point lookups on the primary key columns in the tables Production. The two key stored procedures for inserting sales order and updating shipping details are as follows: Ostress is a command-line tool that was developed by the Microsoft CSS SQL Server support team.
This tool can be used to execute queries or run stored procedures in parallel.
See Books Online for more details about index guidelines and guidelines for choosing the right bucket_count. Sales Order Header_inmem has three indexes, which are all HASH indexes for performance reasons, and because no ordered or range scans are needed for the workload.
The indexes on the migrated tables have been tuned for the demo sales order processing workload. Memory utilization in the sample database, both before and after running the demo workload, is discussed in the Section Memory utilization for the memory-optimized tables.
The command-line options for ostress can be seen when simply running without any command-line options.