test.rb

require 'rubygems'
require "dbi"
require 'pg'

dbh = DBI.connect("dbi:Pg:gosu_dev", "erikh")

dbh.do("drop table stuff") rescue nil
dbh.do("create table stuff (id serial primary key, my_string varchar(20), my_integer integer, my_time timestamp)")

chars = ('a'..'z').to_a + ('0'..'9').to_a

dbh.transaction do |trans_dbh|
  sth = dbh.prepare("insert into stuff (my_string, my_integer, my_time) values (?, ?, ?)");
  20000.times do
    str = ""
    rand(20).to_i.times do
      str += chars[rand(chars.size).to_i]
    end
    sth.execute(str, rand(50000).to_i, DateTime.now);
  end
end

dbh.disconnect
 
def time(label, &block)
  a = Time.now.to_f
  block.call
  b = Time.now.to_f
  puts "#{ label } #=> #{ b - a }"
end

pgconn = PGconn.new(:user => 'erikh', :dbname => 'gosu_dev');
 
time('pgconn') do
  size = 0
  pgconn.query('select * from stuff').each{|row| size += 1}
  puts size
end

# you can also do this on dbh and sth
DBI.convert_types = true

dbh = DBI.connect("dbi:Pg:gosu_dev", "erikh")

time('dbi with type conversion') do
  puts dbh.convert_types
  size = 0
  dbh.select_all("select * from stuff").each {|row| size += 1 }
  puts size
end

DBI.convert_types = false

dbh = DBI.connect("dbi:Pg:gosu_dev", "erikh")

time('dbi without type conversion') do
  puts dbh.convert_types
  size = 0
  dbh.select_all("select * from stuff").each {|row| size += 1 }
  puts size
end

__END__

20000
pgconn #=> 0.195024013519287
true
20000
dbi with type conversion #=> 2.12542200088501
false
20000
dbi without type conversion #=> 0.764398813247681