1

Background: We are using mariadb server with a mariadb-connector-cpp for establishing connection between clinet&server. At max we have 8000 rows of data (total num chars is 250 per row).

Exploring ways to optimize memory usage at the client side using setFetchSize(xxx) its info available in https://mariadb.com/kb/en/about-mariadb-connector-j/#streaming-result-sets.

Question 1: whether the results of the executed query stored on the server? Will be retrieved by the client on calling ResultSet.next() which internally communicates with the server?

Ex. can be found in the below section: with procedures "7.2.3 Using a Statement for a Stored Procedure That Returns a Result Set" from https://downloads.mysql.com/docs/connector-cpp-1.1-en.a4.pdf.

Question 2: Does setFetchSize() work only with Procedures implemented at the server or does normal executeQuery('select * from *') also has the same behavior retrieving a sub-set of results from the server?

Ex:

sql::Driver* driver = sql::mariadb::get_driver_instance();
sql::SQLString url("jdbc:mariadb://127.0.0.1:3306/mysql");

sql::Properties properties;
properties["user"] = CUCP_MARIADB_SERVER_USERNAME;
properties["password"] = CUCP_MARIADB_SERVER_PASSWORD;

//establish connection
std::unique_ptr<sql::Connection> conn(driver->connect(url, properties));

//create a statement and execute
std::unique_ptr<sql::Statement> stmnt(conn->createStatement());
stmnt->setFetchSize(1000);
sql::ResultSet *res = stmnt->executeQuery("select User from user"); // for ex: 1000 entries are there in User column

// On every next, a request sent internally sent to server to get the results?
while (res->next()) {
    std::cout << ", user = " << res->getString(1);
}
delete res;

Internally how mariadb-client-connector-cpp client code fetch data? We want to reduce memory usage at the client application with a stream mechanism if possible.

0

1 Answer 1

1

The results of a query are not buffered anywhere on the server. That goes for the currently running statement. And the server simply doesn't run the second query until you have finished reading the first one and then called ResultSet.next, at which point the query is actually executed.

Even the first query is not stored anywhere, assuming you have a non-blocking plan (ie no sort or hashing). It's fed directly from the storage engine row-by-row.

setFetchSize meanwhile can optimize fetches in certain instances, by caching that many rows ahead in the client's memory. The server does not play a part in this. It's also only a hint, and is not always followed. If you want to reduce memory consumption (for some odd reason) then do not use this feature at all, as it sacrifices memory usage in order to fetch the rows quicker.

Having said that, your total resultset size is only about 2Mb, so not really worth thinking about unless you are working on some $10 chip from the 1990s.

2
  • Thanks for the response. The point is that there is no storage fo results at the server. The first 2 statements are confusing. "The results of a query are not stored anywhere on the server." - Means no storage and buffering of results. "The server simply doesn't run the second query until you have finished reading the first one" - This means that the server will not run another query even though no buffering of the results. Not sure why the server doesn't run if it is not buffering/storing results.
    – Rakesh
    Dec 5, 2023 at 5:21
  • Reworded it a bit. If you are still reading the first resultset it can't run the second one as it' can't buffer the results anywhere, it has to send them straight to you. Dec 5, 2023 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.