MySQL Forums
Forum List  »  Connector/C++

Performance of PreparedStatements vs. vanilla Statements with SELECT queries on large datasets
Posted by: Kenny Peng
Date: July 15, 2009 10:03AM


I was investigating the performance of MySQL Connector/C++ vs. MySQL++ (which is what we use now). I noticed some odd performance with the PreparedStatement's on SELECT queries on large data sets. I dug a bit deeper and noticed that ResultSet is implemented basically as a singly-linked-list, and the PreparedResultSet by default, returns a scrollable result set. Thus, when doing repeated calls on results->next(), it ends up using mysql_stmt_data_seek repeatedly (which always starts from the beginning of the set and iterates to the given row), turning what should be a linear run to quadratic. Simple vanilla statements do not have this problem, it seems.

So I am curious whether this is an intentional implementation of how PreparedStatement's should function and whether I am using PreparedStatement's in the intended fashion?

Here's the code snippet that I am using, this performs orders of magnitude worse than the alternative commented code. My conclusions were drawn from running callgrind on the output and inspecting the debug builds of the mysql-connector-c++ and mysql-client library.

    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    sql::Connection* conn = driver->connect(...);

    // sql::Statement* statement = conn->createStatement();
    sql::PreparedStatement* statement = conn->prepareStatement("SELECT some_column FROM some_table WHERE date = ?;");
    statement->setString(1, "20090701");
    // sql::ResultSet* results = statement->executeQuery("SELECT some_column FROM some_table WHERE date = '20090701';");
    sql::ResultSet* results = statement->executeQuery();

    while (results->next())

    delete results;
    delete statement;
    delete conn;

Options: ReplyQuote

Written By
Performance of PreparedStatements vs. vanilla Statements with SELECT queries on large datasets
July 15, 2009 10:03AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.