Performance of PreparedStatements vs. vanilla Statements with SELECT queries on large datasets

[Kenny Peng]

Hi,

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;