Re: Of large database tables and high memory usage.

I'm certain that you will find that most table-wide queries will be unbufferable. The reason is that for the most part, buffering is a result-data dependant operation. Another issue is that some standard query statements actually invoke a view, which itself is a remapping of the data in the DB.

I've used DBI and Rose::DB::Object and found that they are optimized for highly selective queries that limit the results to a RAM memory container. Rose::DB::Object::Manager abstraction will provide iterative method capabilities, but since it is build on top of DBI, it will not provide "data window" memory management.

I suggest that your applications will benefit from intelligent design rather than generic tools. I'd be very interested in any discussion that suggests otherwise because I've experienced similar design requirements.

Comment on Re: Of large database tables and high memory usage.

Replies are listed 'Best First'.
Re^2: Of large database tables and high memory usage. by mseabrook (Beadle) on Jul 27, 2007 at 19:47 UTC
I suggest that your applications will benefit from intelligent design rather than generic tools. I tend to follow that philosophy, but there are other programmers working on this codebase and it's sometimes unreasonable to ask the same of them. If I can put together a tool that presents a simple way of doing X, while solving problems Y and Z, we'll wind up with more consistent code, and we'll be able to focus on the higher levels of abstraction while creating programs. Historically, this has come at the expense of performance, though. Anyways, it looks like the high memory usage that I'm seeing might be a MySQL-exclusive phenomenon.	[reply]
Re^2: Of large database tables and high memory usage. by Anonymous Monk on Aug 01, 2007 at 00:05 UTC
I've used DBI and Rose::DB::Object and found that they are optimized for highly selective queries that limit the results to a RAM memory container. Rose::DB::Object::Manager abstraction will provide iterative method capabilities, but since it is build on top of DBI, it will not provide "data window" memory management. Some DBI DBDs provide options for this sort of thing. For example, DBD::mysql provides a "mysql_use_result" attribute to prevent a total transfer of data to the client on execute(). (More info.) (And incidentally, Rose::DB provides access to this attribute.)	[reply]