[Ruby-DBI-devel] (long) Speed differences test

[Gabriel <ga...@de...>]

I have cobbled together a short program to show speed differences
between ruby-postgres and ruby-dbi.  Again, it should be the case that
there is a SOME difference in speed, but I am showing a difference of
1000x.  I tried running this on a medium sized table (470,000 rows), and
20 hours later the DBI portion hadn't finished running.  This is of
course a contrived example, but it is typical, and doesn't make a
difference whether all rows are fetched at once or they are fetched in
bits, whether I use the sth or dbh versions, whether I use each,
fetch_array, or fetch_hash, etc.  I hope this test is properly written;
I adapted it from some production code that ran in both forms.

On a dual processor xeon 450, I got this (on about 1700 rows):
Ruby-Postgres:
0.056454
DBD::Postgres:
44.038441

On a dual processor athlon 1.2 ghz:
Ruby-Postgres:
0.017819
DBD::Postgres:
20.341243

  CODE STARTS HERE --->
  require 'postgres'
  require 'dbi'

  class QueryTiming

    def initialize
        @dbi_conn = DBI.connect('dbi:Pg:gobo','username','password')
	@pg_conn = PGconn.connect('localhost',5432,'','','gobo','username','password')
    end

    def pg_query(sql)
       rows = @pg_conn.query(sql)
       rows.each {|row|
         # do nothing on each row
       }
    end

    def dbi_query(sql)
       sth = @dbi_conn.prepare(sql)
       sth.execute()
       sth.each {|row|   sth.each {|row|
          # do nothing on each row
       }
       sth.finish()
    end

  end

 if (__FILE__ == $0)
    sql = "select doozerid,
     doozernumber, firstname, lastname,
     middlename from doozer"
    qt = QueryTiming.new
    s_time = Time.now()
    qt.pg_query(sql)
    puts("Ruby-Postgres:",Time.now() - s_time)
    s_time = Time.now()
    qt.dbi_query(sql)
    puts("DBD::Postgres:",Time.now() - s_time)
 end