Hi, when using a prepared statement (using MySQL adapter) containing
two or more variables, Sequel does a "SET" query for each variable,
this is:
#prepare:
PREPARE ps FROM "SELECT etag FROM storage WHERE username = ? AND
domain = ?";
#call:
SET @u = "ibc";
SET @d = "example.org";
EXECUTE ps USING @u, @d;
This could be improved by using a single SET query (and it works):
#prepare:
PREPARE ps FROM "SELECT etag FROM storage WHERE username = ? AND
domain = ?";
#call:
SET @u = "ibc", @d = "example.org";
EXECUTE ps USING @u, @d;
It reduces the CPU used by MySQL in high load environments (tested
with some benchmarks).
I attach a patch for "lib/sequel/adapters/mysql.rb" which improves the
performance as explained above. Hope it's useful.
--
Iñaki Baz Castillo
<[email protected]>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
--- mysql.rb_original 2009-09-08 10:09:57.000000000 +0200
+++ mysql.rb 2009-09-08 10:24:26.000000000 +0200
@@ -224,7 +224,7 @@
_execute(conn, "PREPARE #{ps_name} FROM '#{::Mysql.quote(sql)}'", opts)
end
i = 0
- args.each{|arg| _execute(conn, "SET @sequel_arg_#{i+=1} = #{literal(arg)}", opts)}
+ _execute(conn, "SET " + args.map {|arg| "@sequel_arg_#{i+=1} = #{literal(arg)}"}.join(", "), opts) unless args.empty?
_execute(conn, "EXECUTE #{ps_name}#{" USING #{(1..i).map{|j| "@sequel_arg_#{j}"}.join(', ')}" unless i == 0}", opts, &block)
end
end
