In article <7tbehqFn76U2 / mid.individual.net>,
 Robert Klemme <shortcutter / googlemail.com> wrote:
> > So then I tried Ruby:
> > 
> >    require "mysql"
> >    my = Mysql.new(...)
> >    st = my.prepare("SELECT * from my_table")
> >    st.execute
> >    while row = st.fetch do
> >       ...
> >    end
> > 
> > That only uses about 130 MB of memory!?
> > 
> > The above was on a 64-bit machine. On a 32-bit machine, the numbers are 
> > 185 MB for C, Perl, and Python, and 103 MB for Ruby.
> > 
> > How is Ruby able to do so much better on memory usage?
> > 
> > I did not test speed, but I think the Ruby version also ran a little 
> > faster than the others.
> 
> It appears that you employ two fundamentally different approaches: the 
> description of your Perl and Python versions sounds as if you load the 
> *whole table* in one data structure and only then iterate or process it. 
>   The Ruby code fetches a single row at a time (or at most a few rows if 
> there is caching going on behind the scenes).  Well, if my assessment is 
> correct, you have your reason. :-)

Generally, low level mysql libraries provide two ways to get results 
from the server after a query is executed. In the C library, these are 
called mysql_store_results and mysql_use_results.

The former reads the entire results set into a data structure. The 
program then typically calls a fetch_row function that iterates over the 
rows in that results set.

The latter (use_results) is also used with a row iteration function, but 
only fetches the rows from the server as the program calls the row 
iterator.

The execute function in the Ruby mysql library appears to use the 
store_results method. It is during the execution of execute, for 
instance, that the memory usage rises from something insignificant to 
the final 130 MB. If it were using the use_results method, memory usage 
would not noticeably rise while reading the results.

Anyway, in all four (Ruby, Perl, Python, C) I am using the store_results 
form, so as to read the whole table into memory.


-- 
--Tim Smith