Bulk SELECT fetches

Bulk `SELECT` fetches
Prev	Chapter 3. Executing SQL statements	Next

Bulk `SELECT` fetches

fetch() retrieves one row from a SELECT resultset at a time. This is fine for small queries, but each call to fetch() involves non-trivial amount of overhead, that's not very efficient when processing large results. Using fetch_vectors() is more effective with large resultsets. fetch_vectors() implements fetching of multi-row resultsets.

auto stmt=conn->execute("SELECT int_col, varchar_col, FROM tmptbl1 ORDER BY int_col");

std::pair<std::vector<int>, std::vector<x::sql::bitflag>> n;
std::pair<std::vector<std::string>, std::vector<x::sql::bitflag>> str;

size_t c;

while ((c=stmt->fetch_vectors(1000, 0, n, 1, str)) > 0)
{
    for (size_t i=0; i<c; ++i)
    {
        // ...
    }
}

fetch_vectors() differs from fetch() as follows.

The first parameter is a positive row array size.
Like fetch(), columns are specified by 0-based column number, or a name. The value of each column gets places into the first a std::vector instead of a single value, the first std::vector in the std::pair, with the second vector specifying a null value indicator, a std::vector<x::sql::bitflag> instead of a single bool value (the specialized std::vector<bool> would not be very efficient here).
fetch_vectors() resizes all vectors to the specified array row size value, and fetches up to the given number of rows, and returns the number of rows fetched from the resultset (which will be 0 when there are no more results). The above example fetches up to a 1000 rows at a time.
fetch_vectors() returns with all vectors resized to the specified array row size, even if fewer rows were ultimately returned from the resultset.

The vectors specify the value for their corresponding columns, in each row. They are column vectos. Indirectly specifying columns with fetch_vectors() is similar how its done with fetch(), except that the map value is a std::pair of vectors:

std::map<std::string, std::pair<std::vector<std::string>,
    std::vector<x::sql::bitflag>>> columns;

// Side effect of operator[] is to insert these columns, that's
// what we want here:

auto &int_col=columns["int_col"];
auto &varchar_col=columns["varchar_col"];

size_t c;

while ((c=stmt->fetch_vectors(1000, columns) > 0)
{
    std::vector<int> &n=int_col.first;
    std::vector<x::sql::bitflag> &n_null=int_col.second;
    std::vector<std::string> &str=varchar_col.first;
    std::vector<x::sql::bitflag> &str_null=varchar_col.second;

    for (size_t i=0; i<c; ++i)
    {
        // If n_null[i] or str_null[i], the corresponding value is null,
        // otherwise see n[i] and str[i]
    }
}

fetch_vectors() is better than with fetch() for large resultsets, and fetch_vectors_all() is better than fetch_vectors():

std::map<std::string, std::pair<std::vector<std::string>,
    std::vector<x::sql::bitflag>>> columns;

// Side effect of operator[] is to insert these columns, that's
// what we want here:

auto &int_col=columns["int_col"];
auto &varchar_col=columns["varchar_col"];

stmt->fetch_vectors_all(1000,
    [&]
    (size_t c)
    {
        std::vector<int> &n=int_col.first;
        std::vector<x::sql::bitflag> &n_null=int_col.second;
        std::vector<std::string> &str=varchar_col.first;
        std::vector<x::sql::bitflag> &str_null=varchar_col.second;

        for (size_t i=0; i<c; ++i)
        {
           // If n_null[i] or str_null[i], the corresponding value is null,
           // otherwise see n[i] and str[i]
        }
   }, columns);

fetch_vectors_all() takes an additional lambda/functor parameter after the row size, and before the column parameters. fetch_vectors_all() is equivalent to repeatedly calling fetch_vectors() until it returns 0, and invoking the lambda/functor each time, passing the number of retrieved rows as its sole parameter.

The lambda/functor presumably captures the returned value vectors by reference, however the lambda/functor must not modify the value vectors, it can only read them. Modifying the actual values in the vectors is fine, if necessary, just that the vectors themselves cannot be resized, cleared, etc... fetch_vectors_all() returns the entire resultset, in chunks, with a minimum of overhead. All requested columns are tied to the given vector buffers once, and are not rebound with every fetch().

The lambda/functor is allowed to throw an exception, to abort processing of the resultset.

Note

The underlying ODBC API is a C API. Full buffers must be allocated for each fetched value. Retrieving 1000 rows of a VARCHAR(100) column requires allocating char[100*1000] worth of memory. Afterwards, 1000 values gets copied to the 1000 std::strings, which uses additional memory.

The char[100*1000] buffer gets freed only after the x::sql::statement goes out of scope and gets destroyed, or when another call to one of the fetch methods gets made. All buffers used by a previous fetch call get freed before allocating buffers for the next fetch.

The temporary buffers are needed only for C++ specific value types, namely std::strings and bool resultset values. Natural numeric values — ints, floats, and other C-compatible types — do not require intermediate buffers.

Prev	Up	Next
Limiting the number of rows fetched	Table Of Contents	Creating scrollable cursors