Improved Data Load Performance

Adaptive Server 15.7 SP100 improves data load performance by enabling ins_by_bulk optimization criteria.  ins_by_bulk enables faster data loading by using bulk-data row inserts for insert statements.

When the data load optimization criteria is enabled with the set statement, Adaptive Server runs subsequent insert...select or batch insert statements with this optimization. The scope of the statement-level directive enabled via the abstract plan is limited to the statement.

This optimization improves data load performance on the target table with or without indexes.

• Enable Data Load Optimization
  The optimization criteria ins_by_bulk improves data load performance.
• Tables with Indexes
  Data load optimization with a target table that has indexes efficiently loads data pages while maintaining the index.
• Viewing Queries with bulkInsertRowCount
  show_cached_plan_in_xml displays the cached execution plan with bulkInsertRowCount to indicate the bulk insert mode. The number enclosed in the bulkInsertRowCount tag is the row count inserted under bulk insert mode.
• Batch Insert Optimization
  Adaptive Server version 15.7 SP100 introduces batch insert optimization when the insert statement is dynamically prepared and executed as a batch via Open Database Connectivity (ODBC).
• Restrictions
  Data load optimization is not followed when certain scenarios are met.