-
-
Save metaskills/557678 to your computer and use it in GitHub Desktop.
require 'bench_press' | |
require 'odbc' | |
extend BenchPress | |
author 'Ken Collins' | |
summary 'Best way to fetch results from an ODBC statement handle.' | |
reps 1000 | |
@con = ODBC.connect 'mc2008', 'rails', '' | |
def select_all_from_topics | |
sth = @con.run "SELECT [topics].* FROM [topics]" | |
yield(sth) | |
sth.drop | |
end | |
measure "Loop with using local row var with fetch" do | |
select_all_from_topics do |sth| | |
rows = [] | |
while row = sth.fetch | |
rows << row | |
end | |
rows | |
end | |
end | |
measure "At one time with fetch_all" do | |
select_all_from_topics do |sth| | |
sth.fetch_all | |
end | |
end | |
measure "Iterating with each" do | |
select_all_from_topics do |sth| | |
rows = [] | |
sth.each do |row| | |
rows << row | |
end | |
rows | |
end | |
end | |
=begin | |
ruby 1.8.7 (2010-04-19 patchlevel 253) [i686-darwin10.4.0], MBARI 0x6770, Ruby Enterprise Edition 2010.02 | |
---------------------------------------------------------------------- | |
At one time with fetch_all 0.302410125732422 secs Fastest | |
Loop with using local row var with fetch 0.302965879440308 secs 0% Slower | |
Iterating with each 0.320544958114624 secs 5% Slower | |
ruby 1.8.7 (2010-08-16 patchlevel 302) [i686-darwin10.4.0] | |
---------------------------------------------------------------------- | |
At one time with fetch_all 0.304914951324463 secs Fastest | |
Loop with using local row var with fetch 0.308919906616211 secs 1% Slower | |
Iterating with each 0.32401704788208 secs 5% Slower | |
ruby 1.9.1p378 (2010-01-10 revision 26273) [i386-darwin10.4.0] | |
---------------------------------------------------------------------- | |
At one time with fetch_all 0.309512853622437 secs Fastest | |
Loop with using local row var with fetch 0.311048030853271 secs 0% Slower | |
Iterating with each 0.322368860244751 secs 3% Slower | |
ruby 1.9.2p0 (2010-08-18 revision 29036) [x86_64-darwin10.4.0] | |
------------------------------------------------------------------------------------ | |
Loop with using local row var with fetch 0.3074948787689209 secs Fastest | |
At one time with fetch_all 0.30851030349731445 secs 0% Slower | |
Iterating with each 0.3212449550628662 secs 4% Slower | |
=end |
Let me fix my memprof install in my rvm environment since I was getting make errors. By the way, I'm using freetds-0.82 with the rails 2-3-stable branch when running this gist.
Forget that, my laptop is 32 bit. memprof doesn't support 32 bit yet. But either way, I was running using the latest 2-3-stable branch of both rails and the sql server adapter with freetds 0.82, ruby-odbc 0.99991 gem, and unixODBC-2.2.14.
Wow, I just did several more iterations switching between rails/adapter at master and rails/adapter at 2-3-stable and the numbers are way too inconsistent to draw any conclusions. (At least on my 32 bit ubuntu installation of ruby 1.8.7 (REE))
2-3-stable of both the adapter and rails:
"At one time with fetch_all" is up to 15% faster over 1,000 repetitions
At one time with fetch_all 0.79194712638855 secs Fastest
Loop with using local row var with fetch 0.910768985748291 secs 13% Slower
Iterating with each 0.935348987579346 secs 15% Slower
"At one time with fetch_all" is up to 9% faster over 1,000 repetitions
At one time with fetch_all 0.80233097076416 secs Fastest
Iterating with each 0.861062049865723 secs 6% Slower
Loop with using local row var with fetch 0.884737968444824 secs 9% Slower
"At one time with fetch_all" is up to 7% faster over 1,000 repetitions
At one time with fetch_all 0.884022951126099 secs Fastest
Iterating with each 0.945185899734497 secs 6% Slower
Loop with using local row var with fetch 0.959433078765869 secs 7% Slower
with latest master for both sql adapter and rails:
"Loop with using local row var with fetch" is up to 13% faster over 1,000 repetitions
Loop with using local row var with fetch 0.834299087524414 secs Fastest
Iterating with each 0.935596227645874 secs 10% Slower
At one time with fetch_all 0.961366891860962 secs 13% Slower
"At one time with fetch_all" is up to 3% faster over 1,000 repetitions
At one time with fetch_all 0.89686393737793 secs Fastest
Iterating with each 0.899981021881104 secs 0% Slower
Loop with using local row var with fetch 0.931588888168335 secs 3% Slower
"Iterating with each" is up to 15% faster over 1,000 repetitions
Iterating with each 0.782932043075562 secs Fastest
At one time with fetch_all 0.838279008865356 secs 6% Slower
Loop with using local row var with fetch 0.924322128295898 secs 15% Slower
"Iterating with each" is up to 17% faster over 1,000 repetitions
Iterating with each 0.79145884513855 secs Fastest
At one time with fetch_all 0.900851011276245 secs 12% Slower
Loop with using local row var with fetch 0.962589979171753 secs 17% Slower
"Iterating with each" is up to 10% faster over 1,000 repetitions
Iterating with each 0.805086851119995 secs Fastest
At one time with fetch_all 0.888291835784912 secs 9% Slower
Loop with using local row var with fetch 0.903757095336914 secs 10% Slower
"Loop with using local row var with fetch" is up to 15% faster over 1,000 repetitions
Loop with using local row var with fetch 0.852494955062866 secs Fastest
At one time with fetch_all 0.894145965576172 secs 4% Slower
Iterating with each 1.00522780418396 secs 15% Slower
Good point, I'm using the 0.83 dev as of last week.