The goal of bulk fetching is to minimize the response time for a query.

One of the ways to reduce response time is to minimize expensive network traffic between the server and client. It can be useful when working with recordsets with a large number of rows.

SQLAPI++ Library provides support for bulk row fetching, which means that multiple records can be retrieved at once during a single fetch, rather than retrieving one record at a time from the data source. To set bulk row fetching you should set PreFetchRows using SACommand::setOption before the command execution. It is safe to use PreFetchRows option for any DBMS. If DBMS does not support bulk row fetching then this option will be ignored.

SACommand cmd(&con);
cmd.setCommandText("Select * from TEST_BULK");

// this instructs the library to allocate a buffer for 100 rows
// and, using respective native API, request data from server in pages of 100 rows
cmd.setOption("PreFetchRows") = "100";

cmd.Execute();
while(cmd.FetchNext())
{
    // process result set row by row as usual
}

The value of PreFetchRows option specifies how many rows you wish to retrieve during a single fetch when using bulk row fetching. The default value is "1"; it means that you are not using bulk row fetching.

See complete example to compare time expended on fetching rows with and without bulk mode.

Need Help?
Send an e-mail to support@sqlapi.com if you want to ask code-level questions, if you’re stuck with a specific error or need advise on the library best practices.