Run-time decision of bulk insert for massive data loading

• US 9,665,612 B2
• Filed: 05/29/2014
• Issued: 05/30/2017
• Est. Priority Date: 05/29/2014
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Ma, Xiaobin, Schneider, Peter, Mottupalli, Srinivasan
• Primary Examiner(s): Nguyen, Kim
• Application Number:
  US14/289,994
• Publication Number:
  US 20150347470A1
• Time in Patent Office: 1,097 Days
• Field of Search: 707711, 707803
• CPC Class Codes: G06F 16/2372 Updates performed during offline database operations

Abstract

A method can include initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode; making a run-time decision on whether to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows from the base table during execution of the database command; and inserting at least one row into the target table using an insert mode based on the run-time decision.

Claims

1. A method comprising:

initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode;

detecting a run-time statistic including a number of row buffers filled with rows from the base table;

making a run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows during execution of the database command;

inserting a set of rows into the target table using the BULK insert mode based on the run-time decision;

determining at least one more rows remains in one or more buffers; and

inserting the one or more rows in a non-bulk insert mode and updating one or more indexes corresponding to the one or more rows in a serial mode.

2. The method of claim 1, wherein making the run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode comprises:

converting the non-bulk insert mode to a BULK insert automatically when a threshold number of row buffers are filled with rows from the base table during execution of the database command.

3. The method of claim 2, further comprising:

allocating a row buffer using a row buffer manager;

reading a first row from the base table; and

building the row read from the base table into the allocated row buffer.

4. The method of claim 3, further comprising:

reading a second row from the base table;

based on determining that the row buffer is full and that the threshold number of row buffers are not filled, allocating a second row buffer using the row buffer manager; and

building the second row read from the base table into the second row buffer.

5. The method of claim 3, further comprising:

reading a second row from the base table;

based on determining that the row buffer is not full, obtaining the beginning position in the row buffer that is free space; and

building the second row read from the base table into the row buffer at the obtained position.

6. The claim 3, wherein an allocated row buffer is the size of a data page of the target table.

7. The method of claim 2, further comprising:

inserting all buffered rows built in the row buffers into the target table using the BULK insert mode when the threshold number of row buffers are filled; and

updating indexes associated with the buffered rows in parallel with the inserting.

8. The method of claim 7, further comprising:

cleaning up data structures used during the BULK insert after all the buffered rows have been inserted into the target table.

9. The method of claim 1, further comprising:

receiving an indication that use of a BULK insert mode is feasible for the database command.

10. A system comprising:

one or more processors; and

a non-transitory computer-readable medium comprising instructions, which when executed by at least one processor, configure the one or more processors to perform operations comprising;

executing a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode;

detecting a run-time statistic including a number of row buffers filled with rows from the base table;

making a run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows during execution of the database command;

inserting a set of rows into the target table using the BULK insert mode based on the run-time decision;

determining at least one more rows remains in one or more buffers; and

inserting the one or more rows in a non-bulk insert mode and updating one or more indexes corresponding to the one or more rows in a serial mode.

11. The system of claim 10, wherein making the run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode further comprises:

converting the non-bulk insert mode to a BULK insert automatically when a threshold number of row buffers are filled with rows from the base table during execution of the database command.

12. The system of claim 11, wherein the operations further comprise:

allocating a row buffer using a row buffer manager;

reading a first row from the base table; and

building the row read from the base table into the allocated row buffer.

13. The system of claim 12, wherein the operations further comprise:

reading a second row from the base table;

based on determining that the row buffer is full and that the threshold number of row buffers are not filled, allocating a second row buffer using the row buffer manager; and

building the second row read from the base table into the second row buffer.

14. The system of claim 11, wherein the indication that use of a BULK insert mode is feasible for the database command is based on querying a list of commands that are feasible for use of the BULK insert mode.

15. The system of claim 10, wherein the operations further comprise:

inserting all buffered rows built in the row buffers into the target table using the BULK insert mode when the threshold number of row buffers are filled; and

updating indexes associated with the buffered rows in parallel with the inserting.

16. A non-transitory computer-readable medium comprising instructions, which when executed by at least one processor, configure the at least one processor to perform operations comprising:

initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table, the database command associated with a non-bulk insert mode;

detecting a run-time statistic including a number of row buffers filled with rows from the base table;

making a run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode based on the number of row buffers filled with rows during execution of the database command;

inserting a set of rows into the target table using the BULK insert mode based on the run-time decision;

determining at least one more rows remains in one or more buffers; and

inserting one or more rows in a non-bulk insert mode at one or more indexes corresponding to the one or more rows in a serial mode.

17. The computer-readable medium of claim 16, wherein making the run-time decision to automatically convert the insert mode from the non-bulk insert mode to a BULK insert mode comprises:

converting the non-bulk insert mode to a BULK insert automatically when a threshold number of row buffers are filled with rows from the base table during execution of the database command.

18. The computer-readable medium of claim 17, the operations further comprising:

allocating a row buffer using a row buffer manager;

reading a first row from the base table; and

building the row read from the base table into the allocated row buffer.

19. The computer-readable medium of claim 17, the operations further comprising:

reading a second row from the base table;

based on determining that the row buffer is full and that the threshold number of row buffers are not filled, allocating a second row buffer using the row buffer manager; and

building the second row read from the base table into the second row buffer.

20. The computer-readable medium of claim 17, the operations further comprising:

inserting all buffered rows built in the row buffers into the target table using the BULK insert mode when the threshold number of row buffers are filled; and

updating indexes associated with the buffered rows in parallel with the inserting.