Performance of PreparedStatements vs. vanilla Statements with SELECT queries on large datasets
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 = ?;");
// sql::ResultSet* results = statement->executeQuery("SELECT some_column FROM some_table WHERE date = '20090701';");
sql::ResultSet* results = statement->executeQuery();