From: Todd Gillespie Date: 2001-12-08T10:15:46+09:00 Subject: [ruby-talk:27883] Re: DBI and large result sets "James Britt (rubydev)" wrote: : 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. Sounds like a plan. : 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. Sounds like a good fit for a CURSOR. : 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. That is entirely up to the database and its configuration. Like some others said, here cursors are your best bet. OTOH, the DB is, 9 times out of 10, smarter than you when it comes to caching. So don't worry too much about what it is using. Also, there's no shared mem between the apps, so optimizing your application memory burden is still a fine goal, since there are two burdens at once. : I can live with the performance hit if, in exchange, I can process huge : record sets without needing gobs of memory. The performance hit is mostly a matter of the DB doing really dumb things like scanning half the freakin' disk to find all the elements. DB designers have spent 30 years building smart query systems. I've seen my fill over the last few years of application servers that try to supersede all that work and redo the query elements in-application, to the great suffering of users and administrators alike. So I'd advise you find another way. DBSource would make young girls squeal if you wrote a system that didn't kneecap the database. : 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) Unfortunately, databases are certainly an area where utility is inversely proportional to portability. Any high perf DB-backed application tries to stay as close to the DB as possible. The 'lowest-common-denominator-SQL' approach is generally only used successfully by students and overfed internet consultancies that will go out of business next year. I highly recommend you look into the path started by the OpenACS4 fold with their DB abstraction layer; they have implemented a better approach. Happy hacking!