A very good point, and something that everyone serious about database performance should know.

A prepared statement is the same as a piece of compiled code. At the time you prepare it, it gets compiled and cached. This is much faster than execute a SQL statement on fly.

Have said this, I would like to mention another trick related to "compiled" stuffs in database. Many times, you can improve performance of queries against big tables, by creating a view on top of the table. By doing this, the view get "compiled". Later instead of querying the table, you just query the view.

It sounds like nothing, and only difference is that you moved some of you where clauses from your query statement to the create view statement. But actually there is a big difference, as at the time you create the view, your where clauses get "compiled" as part of the create view statement.

I recently had an application's execution time cut 96%, by creating view, along with other tunings.

Database (or poor database design and tuning) is one of the major performance bottle necks you see from time to time nowadays.

In reply to Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by pg
in thread Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by jZed

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.