On Mon, 5 Dec 2005, Brian L. wrote:
I think you’re missing the point on how SQL databases work. Are you
suggesting that we make n queries to pull n items out of the table?
no. in fact most ruby database apis work to support this already. for
example:
harp:~ > cat a.rb
require “sqlite”
db = SQLite::Database::new “db”, 0
p db.execute(“select * from foo”).size
db.execute(“select * from foo”) do |tuple|
p tuple
break
end
harp:~ > ruby a.rb
3
{0=>“1”, “x”=>“1”}
this is one query and saves massively on memory for large queries.
try
something like the above in sqlite or postgres with a result set of
1,000,000
rows and check the memory usage on your machine. by using an iterator
(block)
you will see zero memory usage. if you merely say
result = db.execute sql
memory will vanish.
Regardless of memory usage, this is probably a bad idea, since SQL
communications are expensive compared to, say, garbage collection. A good
solution within the current framework would be to use the limit and offset
specifiers to grab, say 100 entries at a time and iterate over them the
“old” way.
this is very dangerous unless done within a transaction since the
queries will
not be isolated.
There’s no meaningful way to abort an in-progress query over SQL,
nor, as I understand the DBI, incrementally unpack a query (though a lazy
database interface would be pretty neat, I don’t know that it is well
defined with regard to ALL supported database backends).
see example above. for databases which don’t support it can easily be
made to
support the same interface:
def execute sql
if block_given?
conn.execute(sql).each{|tuple| yield tuple}
else
conn.execute(sql)
end
end
regards.
-a
===============================================================================
| ara [dot] t [dot] howard [at] noaa [dot] gov
| all happiness comes from the desire for others to be happy. all misery
| comes from the desire for oneself to be happy.
| – bodhicaryavatara