Hello,
we wanted to rewrite our code which previously used the legacy JDBC API and migrate that to the X DevApi. However, we faced a problem when we tried to compare the performance of the Legacy JDBC API with the X DevAPI.
Attached is the very basic version that just selects a couple of thousand records from a MySQL DB.
The for-loop which creates and executes the select statement runs 10-20x slower using the X DevAPI.
Are we missing something here? What could produce such a significant speed difference between these two versions?
Legacy Code
auto const lDataBaseHostName = gTechnicalConfiguration.GetSetting(utl::Constants::cDataBaseURIName);
auto const lDataBaseUser = gTechnicalConfiguration.GetSetting(utl::Constants::cDataBaseUserName);
auto const lDataBasePassword = gTechnicalConfiguration.GetSetting(utl::Constants::cDataBasePasswordName);
auto const lDataBaseSchema = gTechnicalConfiguration.GetSetting(utl::Constants::cDataBaseSchemaName);
auto* lDriver = sql::mysql::get_driver_instance();
auto const lDataBaseConnection = lDriver->connect(lDataBaseHostName.c_str(), lDataBaseUser.c_str(), lDataBasePassword.c_str());
auto connection = std::shared_ptr<sql::Connection>(lDataBaseConnection);
if (!connection->isValid()) {
throw exc::LogicErrorException("Cannot open DB connection");
}
connection->setSchema(lDataBaseSchema.c_str());
for (auto _ : state) {
std::shared_ptr<sql::Statement> stmt(connection->createStatement());
std::shared_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT * FROM product"));
while (res->next()) { }
}
X DevApi Code
auto const lDataBaseHost = technicalConfiguration.GetSetting(utl::Constants::cDataBaseHost);
auto const lDataBasePort = technicalConfiguration.GetSetting(utl::Constants::cDataBasePort);
auto const lDataBaseUser = technicalConfiguration.GetSetting(utl::Constants::cDataBaseUserName);
auto const lDataBasePassword = technicalConfiguration.GetSetting(utl::Constants::cDataBasePasswordName);
auto const lDataBaseSchema = technicalConfiguration.GetSetting(utl::Constants::cDataBaseSchemaName);
std::shared_ptr<mysqlx::Client> client = nullptr;
client = std::make_shared<mysqlx::Client>(
mysqlx::SessionOption::USER, lDataBaseUser,
mysqlx::SessionOption::PWD, lDataBasePassword,
mysqlx::SessionOption::HOST, lDataBaseHost,
mysqlx::SessionOption::PORT, std::stoi(lDataBasePort),
mysqlx::SessionOption::DB, lDataBaseSchema,
mysqlx::SessionOption::SSL_MODE, mysqlx::SSLMode::REQUIRED,
// Enforced to verify server certificate
mysqlx::ClientOption::POOLING, true,
mysqlx::ClientOption::POOL_MAX_SIZE, 25
);
auto session = client->getSession();
for (auto _ : state) {
mysqlx::SqlStatement statement = session.sql("SELECT * FROM product");
auto retValue = statement.execute();
for (auto lRow : retValue) {
}
}