From: "James Britt (rubydev)" <james@...>
Date: 2001-12-08T06:35:32+09:00
Subject: [ruby-talk:27862] Re: DBI and large result sets

> : Running 
> 
> :    dbh.execute("SELECT * FROM MILLIONS_OF_EMPLOYEES") do |sth|
> :      sth.fetch do |row|
> :        p row
> :      end
> :    end
> 
> : means the local code only has a single row in memory at any given time.
> 
> That's right, if you ignore whatever caching your DB is doing, or the DB
> client libraries.  But your query is still reading the entirety of one or
> many tables.  Your performance is trash, even after optimizing for DBI's
> memory consumption.
> 
> I feel fairly safe in guessing that your application does not print
> several million rows of unordered data directly into the user
> interface, so I'm assuming you do some filtering and operations on your
> millions of rows in-application.  You would be much better off doing this
> with a more complex query or a stored procedure.

What I'm trying write is a Source class for REXML's stream parser.
It should behave as if it were pulling the XML from a file.  REXML comes with
a File-based Source class that reads blocks of characters from the file as
the stream parser progresses.  The file can be arbitrarily large; the parser 
shouldn't know or care, assuming the parser isn't holding on to the data once 
it's been processed. One of the touted benefits of SAX-like XML processing is
that it allows you to process large XML sources without having to hold all the
XML in memory.

In my DbSource, when the stream parser asks for more text, the DbSource
class goes and gets more data (e.g., fetch_many(50)), converts it to XML, and
hands back a string.  So, from the stream parser's point of view, the XML is 
just another file.

Now, if in fact the database driver is pulling back the entire result set, then 
I'm still stuck with the memory burden, regardless of what any parser decides 
to do with it.

I can live with the performance hit if, in exchange, I can process huge
record sets without needing gobs of memory.

A complex query or stored proc would be better for any specific database, but
I want the DBSource class to work with DBI to (ideally) make it (more) 
portable. I don't *think* all databases support syntax for "give me records
m through n" (such as 'LIMIT' in MySQL)

James