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.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.