prep-statement caching (was Re: Beta Schedule)

From: Arndt Brenschede (a..iamos.de)
Date: Thu Mar 27 2003 - 08:25:06 EST

  • Next message: Andrus Adamchik: "Re: Beta Schedule"

    Holger Hoffstätte wrote:

    > I remember - did you implement the statement cache like we discussed? Does
    > it really provide a measurable performance boost? I ask since I'm curious
    > - I do not know what makes prepared statement creation so supposedly slow.

    I did it really cheap by patching the method
    ConnectionWrapper.prepareStatement(String sql).

    It uses the sql-Query String itself as a key
    to a hashmap containing the statements already
    prepared for that connection.

    Furthermore, I had to remove the "close()" methods
    on these statements.

    A more intelligent solution would be to
    use a proxy-object similar to "ConnectionWrapper"
    also for the PreparedStatement interface, because
    it would give full control over the close()
    method.
    I think I would have to go that way when
    hunting for a succesfull unit-test (because
    now I forgot some close-s and get
    "close statement" errors out of the unit
    test suite.

    The effect is really dramatic - I once
    put some numbers in the Bugtracker-
    it's about going down from 5ms to 2ms
    for a simple select query.

    The reason is simply that by reusing
    the prepared statements you save
    the actual "preparation" step, and
    this obviously is what "Prepared" statements
    are about.

    The preparation (that happens when you
    call "prepareStatement") means an additional
    turnaround to the database and the
    Database-Internal Query-Compilation
    from the query string to an execution plan.

    > One important thing about this cache is IMHO that it should be possible to
    > set its size (0 to disable); other than that I think this is a nice
    > feature that should go in - if not for b1 then b2 or whenever it's ready.
    > Passing all existing unit tests & running correctly in the regression app
    > (once it's working again ;) is the go/no-go criterion as far as I'm
    > concerned.

    having a size limit and some sort of least recently used
    algortithm is of course some headache, but I will
    think about it...

    best regards,

    Arndt



    This archive was generated by hypermail 2.0.0 : Thu Mar 27 2003 - 08:25:28 EST