Improved Performance for Data Insertion

These 15.0.2 features can enhance performance for data insertion:

• Fast bcp can copy data into tables with nonclustered indexes or triggers, improving Adaptive Server’s performance for inserting huge volumes of data.
• Adaptive Server version 15.0.2 includes a separate user log cache (ULC) for the session’s temporary database, so multidatabase transactions that include a single user database and the session’s temporary database do not require ULC flushes when the users switch between the databases or if all of the following conditions are met:

  • Adaptive Server is currently committing the transaction.

  • All the log records are in the ULC

  • There are no post-commit log records.

  The configuration option, session tempdb log cache size, allows you to configure the size of the ULC, helping to determine how often it needs flushing.

• When Adaptive Server splits an index or data page, it moves some rows from the original pages to the newly created page. The operation of moving the rows is not logged. Adaptive Server version 15.0.2 uses asynchronous writes to disk that do not require the server to block as it waits for the write to complete. Adaptive Server version 15.0.2 uses these asynchronous writes automatically and requires no configuration on your part.

• Improved throughput of tempdb transactions

• Adaptive Server version 15.0.2 provides post-commit optimization. The server performs two scans of the log: the first scan looks for data page deallocation and unreserved pages, the second scan looks for log page deallocation. These scans are an internal optimization, transparent to users, and are performed automatically; you cannot switch the scans on or off.

  With post-commit optimization, Adaptive Server remembers the “next” log page (in the backward direction) containing these log records. During the post-commit phase, Adaptive Server moves to the “next” page requiring post-commit work after processing records from a page. In a concurrent environment, where many users log their transactions to syslogs at the same time, post-commit optimization can improve the performance of post commit operation by avoiding reads or scans of unnecessary log pages.