Re: hard vs soft parse

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

> I think that there is a mis-print here, surely is should read:
>
> >"A high soft parse rate is important because unnecessary HARD parses
>
> Most applications will have to go through the parse/execute/fetch
> cycle to get data, OLTP systems rarely have a natural design
> that makes it straightforward to code for re-binds, which is how
> you explicitly avoid re-parsing.

Uhm, sure they do if you use a flexible enough language. You either use a a persistent variable to store the cursor from one invocation to the next, or you store cursors in a cache keyed by the query text and look them up on the client side.

The Perl DBI even has this latter method implemented as a native feature, prepare_cached(). It does a prepare if there's a cache miss and stores the cursor in the cache.

Alternatively in perl it's actually easy to build a closure and have a lexically scoped variable at the call site with something like

{
  my $cursor;
  sub selectfromdual = sub { $cursor ||= $dbh->prepare(q{select * from dual}); }

That gives a procedure that returns the same cursor every time using the lexically scoped $cursor which is not available from any other scope.

Personally while this approach looks cleaner I find having an explicit cache of cursors that I can poke around in to be convenient.

-- 
greg