From 2f9a0f75db51993b9e18580697f140d5e36bed96 Mon Sep 17 00:00:00 2001 From: Jeremy Cole Date: Fri, 13 Jul 2012 09:42:48 -0700 Subject: Only use prepared statements when bind variables are present Prepared statements (prepare/execute/close) were being used unnecessarily when no bind variables were present, and disabling prepared statement using prepared_statements:false was principally broken. While bind variables were correctly substituted with prepared_statements:false, the prepared statement interface was still used, costing an extra two round trips per query. In addition to making this behavioral change, I also cleaned up the internals of exec_stmt and exec_without_stmt so that they behave the same (calling log and constructing the ActiveRecord::Result in the same way). Moving the check for binds.empty? to exec_query also will mean that several code paths explicitly calling exec_without_stmt could be cleaned up to once again call exec_query instead. I have also left the check for binds.empty? in exec_stmt, since it is not a private method and could be called directly with an empty binds array. For the sake of clarity in this patch, I have not made those changes. = The previous behavior = When issuing a Foo.find(1) with prepared_statements:true, the bind variable is present in the prepared query, and execute shows a value passed: Connect root@localhost on rails_test Query SET SQL_AUTO_IS_NULL=0 Statistics Query SHOW FULL FIELDS FROM `foos` Query SHOW TABLES LIKE 'foos' Query SHOW CREATE TABLE `foos` Prepare SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = ? LIMIT 1 Execute SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = 1 LIMIT 1 Close stmt Quit When issuing a Foo.find(1) with prepared_statements:false, the bind variable has already been removed and substituted with the value, but the prepared statement interface is used anyway: Connect root@localhost on rails_test Query SET SQL_AUTO_IS_NULL=0 Statistics Query SHOW FULL FIELDS FROM `foos` Query SHOW TABLES LIKE 'foos' Query SHOW CREATE TABLE `foos` Prepare SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = 1 LIMIT 1 Execute SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = 1 LIMIT 1 Close stmt Quit = With this patch applied = When issuing a Foo.find(1) with prepared_statements:true, the bind variable is present in the prepared query, and execute shows a value passed: Connect root@localhost on rails_test Query SET SQL_AUTO_IS_NULL=0 Statistics Query SHOW FULL FIELDS FROM `foos` Query SHOW TABLES LIKE 'foos' Query SHOW CREATE TABLE `foos` Prepare SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = ? LIMIT 1 Execute SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = 1 LIMIT 1 Close stmt Quit When issuing a Foo.find(1) with prepared_statements:false, the bind variable has been removed and substituted with the value, and the query interface is used instead of the prepared statement interface: Connect root@localhost on rails_test Query SET SQL_AUTO_IS_NULL=0 Statistics Query SHOW FULL FIELDS FROM `foos` Query SHOW TABLES LIKE 'foos' Query SHOW CREATE TABLE `foos` Query SELECT `foos`.* FROM `foos` WHERE `foos`.`id` = 1 LIMIT 1 Quit --- .../connection_adapters/mysql_adapter.rb | 74 ++++++++++++---------- 1 file changed, 40 insertions(+), 34 deletions(-) (limited to 'activerecord') diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb index 0b6734b010..2880282c89 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb @@ -279,10 +279,14 @@ module ActiveRecord end def exec_query(sql, name = 'SQL', binds = []) - log(sql, name, binds) do - exec_stmt(sql, name, binds) do |cols, stmt| - ActiveRecord::Result.new(cols, stmt.to_a) if cols - end + # If the configuration sets prepared_statements:false, binds will + # always be empty, since the bind variables will have been already + # substituted and removed from binds by BindVisitor, so this will + # effectively disable prepared statement usage completely. + if binds.empty? + exec_without_stmt(sql, name) + else + exec_stmt(sql, name, binds) end end @@ -339,41 +343,43 @@ module ActiveRecord def exec_stmt(sql, name, binds) cache = {} - if binds.empty? - stmt = @connection.prepare(sql) - else - cache = @statements[sql] ||= { - :stmt => @connection.prepare(sql) - } - stmt = cache[:stmt] - end + log(sql, name, binds) do + if binds.empty? + stmt = @connection.prepare(sql) + else + cache = @statements[sql] ||= { + :stmt => @connection.prepare(sql) + } + stmt = cache[:stmt] + end - begin - stmt.execute(*binds.map { |col, val| type_cast(val, col) }) - rescue Mysql::Error => e - # Older versions of MySQL leave the prepared statement in a bad - # place when an error occurs. To support older mysql versions, we - # need to close the statement and delete the statement from the - # cache. - stmt.close - @statements.delete sql - raise e - end + begin + stmt.execute(*binds.map { |col, val| type_cast(val, col) }) + rescue Mysql::Error => e + # Older versions of MySQL leave the prepared statement in a bad + # place when an error occurs. To support older mysql versions, we + # need to close the statement and delete the statement from the + # cache. + stmt.close + @statements.delete sql + raise e + end - cols = nil - if metadata = stmt.result_metadata - cols = cache[:cols] ||= metadata.fetch_fields.map { |field| - field.name - } - end + cols = nil + if metadata = stmt.result_metadata + cols = cache[:cols] ||= metadata.fetch_fields.map { |field| + field.name + } + end - result = yield [cols, stmt] + result = ActiveRecord::Result.new(cols, stmt.to_a) - stmt.result_metadata.free if cols - stmt.free_result - stmt.close if binds.empty? + stmt.result_metadata.free if cols + stmt.free_result + stmt.close if binds.empty? - result + result + end end def connect -- cgit v1.2.3