20000 rows, times 96 columns, times 10 bytes per column (why 10? Just had to plug in a number) means that you're outputting a document of 18 megabytes even before injecting markup tags. Markup tags could easily add 50% more to your output.

How about breaking up the response into multiple pages? I doubt anyone is going to want to look at a browser filled with 20,000 lines of text. If you're spitting out the text for the purpose of the user saving a file they can manipulate later, how about pre-zipping it, and sending it as a tab delimited text file? That might reduce its size considerably. I just think that rendering an 18-25 megabyte file on screen in a browser is asking a lot of the browser to handle. I could be wrong.

Also, pulling 20k records from a database is going to take some time, and ordering (sorting) them takes some time too. Database interactions might be helped along by configuring your database engine for caching. Throwing memory and disk space at the problem could help from the database side. My mind goes to a book I recently browsed through: Developing Web Applications with Mod_Perl, MySQL, and Memcached. The topic revolved around LAMMP: Linux, Apache, MySQL, Perl... That second 'm' is for Memcache, where you literally throw additional memory and computing power at a problem in a modular way.

Update: If you're unable to extract better performance then maybe you have to examine how the data will be used. Is it adequate to generate one report per day, and make it downloadable?

I'll be interested in hearing what you find.


Dave

In reply to Re: very sloooooow! by davido
in thread very sloooooow! by cliffrubinmusic

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.