From: Alex Fenton <alex@...>
Date: 2008-08-26T13:45:47+09:00
Subject: Re: Which is faster for repetition: Ruby or SQL?

Jason Crystal wrote:

> I was wondering if there was a general consensus for whether it's faster
> to do repetitive queries solely through Ruby (as on an array), or to
> make multiple calls to a SQL database of some sort.
> 
> For example, if I have a dictionary, and I know I need to compare an
> entire paragraph's worth of words to the dictionary, should I query each
> word against the database?  Or load the entire dictionary into a Ruby
> object and index against that?

As Todd says, it will depend on the database, the table structure and 
query, and the connection and interface. It will also depend on the 
relative importance of start-up time (reading a large dictionary into 
Ruby will take some time), memory usage and complexity.

I use SQLite3 as the backend for a fairly complex desktop application. 
The time taken for the SQL backend to execute a query is generally 
trivial compared to the time to convert the rows into ruby objects. Ruby 
objects (in 1.8, less so in 1.9) have significant method-call overhead 
which adds up if very many calls need to be made to complete a single 
request. SQL engines are specialised and optimised for making queries; 
more so if you help by defining the correct INDEXes on TABLEs.

Overall, if performance is an issue, you must make use of benchmark or 
similar:

require 'benchmark'
TIMES = 10_000
# start-up
puts Benchmark.measure { TIME.times { load_ruby_dict } }
puts Benchmark.measure { TIME.times { connect_to_sql_dict } }

# execute
puts Benchmark.measure { TIMES.times { find_using_ruby } }
puts Benchmark.measure { TIMES.times { find_using_sql } }

http://www.ruby-doc.org/stdlib/libdoc/benchmark/rdoc/index.html

alex