odbc_results.rb

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