Loading Data Using Buffers

If you use multiple Import Data (Aggregate Storage) statements to load data values to aggregate storage databases, you can significantly improve performance by loading values to a temporary data load buffer first, with a final write to storage after all data sources have been read.

While the data load buffer exists in memory, you cannot build aggregations or merge slices, as these operations are resource-intensive. You can, however, load data to other data load buffers, and perform queries and other operations on the database. There might be a brief wait for queries, until the full data set is committed to the database and aggregations are created.

The data load buffer exists in memory until the buffer contents are committed to the database or the application is restarted, at which time the buffer is destroyed. Even if the commit operation fails, the buffer is destroyed and the data is not loaded into the database.

Multiple data load buffers can exist on a single aggregate storage database. To save time, you can load data into multiple data load buffers at the same time by using separate MaxL Shell sessions. Although only one data load commit operation on a database can be active at any time, you can commit multiple data load buffers in the same commit operation, which is faster than committing buffers individually.

You can query the database for a list and description of the data load buffers that exist on an aggregate storage database. See Listing Aggregate Storage Data Load Buffers.

Examples:

Example: Load Multiple Data Sources into a Single Data Load Buffer

Assume there are three data files that need to be imported. With aggregate storage databases, data loads are most efficient when all data files are loaded using one import operation. Therefore, load buffers are useful when loading more than one data file.

Use Alter Database (Aggregate Storage) to create a load buffer.

alter database ASOsamp.Basic 
initialize load_buffer with buffer_id 1;

Load data into the buffer, using the Import Data (Aggregate Storage) statement.

import database ASOsamp.Basic data
from server data_file 'file_1'
to load_buffer with buffer_id 1
on error abort;

import database ASOsamp.Basic data
from server data_file 'file_2'
to load_buffer with buffer_id 1
on error abort;

import database ASOsamp.Basic data
from server data_file 'file_3'
to load_buffer with buffer_id 1
on error abort;

Move the data from the buffer into the database.
```
import database ASOsamp.Basic data
from load_buffer with buffer_id 1;
```
The data-load buffer is implicitly destroyed.
Assume that in Step 2, after loading 'file_2' into the load buffer, you decided not to load the data. Because the data is in a buffer and not yet in the database, you would simply use Alter Database (Aggregate Storage) to destroy the buffer without moving the data to the database.
```
alter database ASOsamp.Basic
destroy load_buffer with buffer_id 1;
```

Example: Perform Multiple Data Loads in Parallel

In one MaxL Shell session, load data into a buffer with an ID of 1:

alter database ASOsamp.Basic
initialize load_buffer with buffer_id 1 resource_usage 0.5;

import database ASOsamp.Basic data
from data_file "dataload1.txt"
to load_buffer with buffer_id 1
on error abort;

Simultaneously, in another MaxL Shell session, load data into a buffer with an ID of 2:

alter database ASOsamp.Basic
initialize load_buffer with buffer_id 2 resource_usage 0.5;

import database ASOsamp.Basic data
from data_file "dataload2.txt"
to load_buffer with buffer_id 2
on error abort;

When the data is fully loaded into the data load buffers, use one MaxL statement to commit the contents of both buffers into the database by using a comma separated list of buffer IDs:
```
import database ASOsamp.Basic data
from load_buffer with buffer_id 1, 2;
```