sandman has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to create a "browse" window for MySQL tables in Perl. The user should be able to browse through the records presented to him in a Curses widget. My problem is that the SQL table is pretty large (tens of thousands of records) and sucking it all into memory would not work on computers with less RAM.
Can anyone suggest a way to optimize memory usage for this task?

Thanks in advance.

Replies are listed 'Best First'.
Re: memory usage
by btrott (Parson) on May 25, 2000 at 19:54 UTC
    Another option w/r/t the next-previous buttons is that you can ask for certain ranges of records, using the LIMIT keyword in your SELECT statement. For example: 
        select id, name
    from businesses
    limit 1000

    [download]
    will give you the first 1000 records. If you then want the next 1000, you can use this: 
        select id, name
    from businesses
    limit 999,1000

    [download]
    Start at 999 (the offset of the first record is 0, I believe), give me 1000 records.
[reply]
[d/l]
[select]
Re: memory usage
by t0mas (Priest) on May 25, 2000 at 12:58 UTC
    I would implement som sort of fetch next/previous 1000 records and give it to the user 1000 rows at a time. Maybe add a find or jump-to function.
    Why do anyone want to browse tens of thousands of records?
    /t0mas
[reply]
      To answer your second question, because the tables contain the accounting records of a conglomerate of companies and for some reason the accountants insist that they be able to browse/search through at least the previous two months' records (which amount to a shitload of data).
      I thought of displaying just 1000 records at a time and generate/execute a new SQL query every time the user moves to the next/previous 1000 items myself, but I figured there may be a more elegant way to do it...
[reply]
        OK. I've never played with mySQL (sticking to sturdy ol' Oracle) but I guess you must be able to create a cursor, and do previous/next operations to it.
        Perhapps by testing for KEY_UP AND KEY_DOWN.
        /t0mas
[reply]
Re: memory usage
by Maqs (Deacon) on May 25, 2000 at 20:17 UTC
    Why not to use LIMIT in MySQL query?
    /Maqs.
[reply]

Back to Seekers of Perl Wisdom