I Agree with perrin, the above solution wont scale.

For a web app youre after performance ( sub 2s response for a good app), doing the temp table thing is going to take you over that (with I/O generated by the temp table creation). If you are in a high transaction environment there is the potential you may run out of memory and chew thru' disk (or become I/O bound) with the temp tables being created and destroyed.

Are you able to ditch, or replicate the swish-e functionality?

If you can, you could fetch the results (in one oracle parse) into an array, and set a 'last_record_seen' flag in a cookie. When the user selects the next page, you'd go get all the results again, and only display the elements in the array between 21-40.

If you wanted, you could minimise the Oracle hits by using Storable.pm or Cache . Of course, you then have the trade off between stale information and the length of time to cache results. If new ads are appearing infrequently, it may not be such a hastle.

Unfort, if you ditch Swish-e in favour of a like clause, it precludes the use of an index when you use %variable% which you could use in order to preform a comprehensive search. Have you considered writing a custom package you could call from a query?

This sounds like a legacy system that is having the web bolted on. IMHO, if there are only a few (5+) thousand records, it may be worthwhile putting all the information into the database. This will scale further and be more flexible. You should only get into trouble when you start storing millions of records, the trade off then becomes flexibility v's performance. Typically for lots of content (ala yahoo, excite) there will be pointers in a database to files at the filesystem level, which is where you may be coming from.

This is slightly off topic, but never store graphics in your database for web apps, you'll almost always get a faster result from storing graphics at the filesystem level.

To answer your question directly, use an insert statement and use placeholders if you want to use DBI.

In reply to Re: DBI - Oracle 8 - Load many rows into a temp table w/o using SQL*Loader by Ryszard
in thread DBI - Oracle 8 - Load many rows into a temp table w/o using SQL*Loader by joealba

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.