From: Daniel Berger <djberg96@...>
Date: 2001-05-31T05:11:25+09:00
Subject: [ruby-talk:16050] oracle & threads - need speed

Hi all,

I'm trying to get a series of sql select statements, read in from a text
file, to run concurrently in order to retrieve the results as fast as
possible.  It doesn't appear to actually run any faster using threads or
just sending the queries sequentially.  Am I doing this right?

I can't really post the sql here, but it doesn't really matter.  Put 100
"select sysdate from dual" statements in your sql file if you want.

I took and modified the Thread example on 113-114 of the Hunt book.  Here's
what it looks like.  Thanks in advance for any help.

Regards,

Dan

PS - Got family in the Tampa area - hmmmm...may have to go to the Ruby
Conference

#!/usr/local/bin/ruby -w
require 'oracle'

#++++++++++++++++++++++++++++++++++++++++
# Various constants and global variables
#++++++++++++++++++++++++++++++++++++++++
DB      = 'prodreports'
USER    = 'reportwriter'
PASSWD  = 'reportwriter'
SQLFILE = 'mySql.txt'

Thread.abort_on_exception = true

queries = []
results = []
threads = []

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Grab queries as paragraphs and store each query in the 'queries' array.
Also tried putting the
# thread loop within this loop.  That didn't make much difference as far as
I could tell.
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
$/ = ""
IO.foreach(SQLFILE){ |sql|
   #puts sql
   queries.push sql
}

#+++++++++++++++++++++
# Connect to database
#+++++++++++++++++++++
dbh = Oracle.new(uid=USER, pswd=PASSWD, conn=DB)

#++++++++++++++++++++++++++++++++++++++++
# Run each query in its own thread and store the results
#++++++++++++++++++++++++++++++++++++++++
for query in queries
   threads << Thread.new(query){ |myQuery|
      sth = dbh.exec(myQuery)
      sth.fetch do |v|
         #puts v
         results.push v
      end
      sth.close
   }
end

threads.each{ |aThread| aThread.join }

results.each{ |result| puts result }

#++++++++++++++++++++++++++++++
# Disconnect from the database
#++++++++++++++++++++++++++++++
dbh.logoff