Following values can be used for columnstore_use_import_for_batchinsert:
OFF (0): Do not use cpimport for LDI or INSERT..SELECT
ON (1, default): Use cpimport for LDI or INSERT..SELECT only for non-transactional queries
ALWAYS (2): Use cpimport for LDI or INSERT..SELECT from within a transaction as well as for non-transactional queries
The session variable can be changed for either the current session using the 'set' command,
or for all future sessions using the 'set global' command.
For QA:
Create a table with a few columns and a large data file (e.g. 100k or 200k records) to be imported using LDI.
1. First test the current default behaviour, i.e. columnstore_use_import_for_batchinsert=ON:
- Perform LDI inside a transaction (e.g. by using the START TRANSACTION command, or SET AUTOCOMMIT=0): this should use the regular DML operation. Doing a rollback will undo the load from the table.
- Perform LDI outside the transaction: this will use cpimport for the load. To check the operation uses cpimport, use the linux top command. This operation will also run magnitudes of order faster than the previous one from within a transaction that used DML. Performing a rollback with not undo the load.
2. Test the new behaviour by setting columnstore_use_import_for_batchinsert=ALWAYS:
- Perform LDI inside a transaction: this time, it should use cpimport for the operation. Doing a rollback will not undo the load from the table.
- Perform LDI outside the transaction: this should again use cpimport for the load. Performing a rollback with not undo the load.
Same tests can be repeated for INSERT ... SELECT.
Task
Major
MCOL-4007 Add an option to allow using cpimport for LDI and INSERT..SELECT in a transaction
Following values can be used for columnstore_use_import_for_batchinsert:
OFF (0): Do not use cpimport for LDI or INSERT..SELECT
ON (1, default): Use cpimport for LDI or INSERT..SELECT only for non-transactional queries
ALWAYS (2): Use cpimport for LDI or INSERT..SELECT from within a transaction as well as for non-transactional queries
The session variable can be changed for either the current session using the 'set' command,
or for all future sessions using the 'set global' command.
For QA:
Create a table with a few columns and a large data file (e.g. 100k or 200k records) to be imported using LDI.
1. First test the current default behaviour, i.e. columnstore_use_import_for_batchinsert=ON:
2. Test the new behaviour by setting columnstore_use_import_for_batchinsert=ALWAYS:
Same tests can be repeated for INSERT ... SELECT.