rails/rails — pull request #7042 by jeremycole (2012-07-13 17:01:34+00:00)
Title: 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