I am not cure I have understood all of your meditation, but let's see if I can address the part titled Issues with runtime SQL generation .

In your example you show a select statement that grabs certain fields (@field) from a table if the CGI variable has been passed in, based on certain conditions (@where).

I suppose an attempt to optimize would have a BEGIN statement generate all of the possible permutations of @fields and @where. The load time (i.e. time to execute the BEGIN) and the memory required to store the results could be considerable, based on the size of each of the arrays.

About the only database tuning that I can think of for a select statement would be indexing on the contrained variables; in this example, weight would be an indexed column.

Caching queries might be possible and even quite efficient, but I don't know if that type of thing can easily be generalized. In this example of a simple select statement, it's likely easier to just generate the SQL and grab the data than to search through a collection of cached queries, based on which fields and constraints have been requested.

Is there a more complicated example to explore the issues that you're interested in pursuing?

--t. alex

"Of course, you realize that this means war." -- Bugs Bunny.

In reply to Re: Eliminating Dynamic SQL Generation with Decision Matrices for Tighter Large Scale Applications by talexb
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.