...generating SQL statements beforehand... seems to conflict with the desire of many users to perform ad-hoc queries, even if constrained by a simple web form.

It all depends on the size of the database, the type of queries, and the load that is placed on the database (i.e. the number of parallel instances of such ad-hoc queries).

Personally I've worked on several large systems (for example the now defunct eCircles.com site) where all access to the database is via pre-defined stored procedures (which is equivalent to pre-defined queries, really). Obviously it requires some thought to define the set of operations that users should be allowed to perform, but it has the huge advantage of separating the SQL from the application. If a query performs badly it can be tuned by the DBA (or the SQL developper) without touching the perl code (as long as the input and output stays the same, of course).
The queries can also be analyzed in isolation to ensure decent performance.

It is, IMHO, a must.

Michael

In reply to Re: Re: Eliminating Dynamic SQL Generation with Decision Matrices for Tighter Large Scale Applications by mpeppler
in thread Eliminating Dynamic SQL Generation with Decision Matrices for Tighter Large Scale Applications by princepawn

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.