Re: [Sqlgrey-users] accepting mail if sqlgrey is down

[Riaan K. <ria...@gm...>]

On 04/02/07, Dave Strickler <dst...@ma...> wrote:
>
>  Riaan,
>
> Not being a Perl programmer on this end, can you explain the downside of
> not using "prepair_cached"? I would assume this means it would need to
> reconnect to the SQL server, or am I missing the point? What does
> prepair_cached buy you in performance?
>
> Dave Strickler
>

I'm no expert, but I'm a fair googler!  This page has an interesting
paragraph on this topic:
http://www.saturn5.com/~jwb/dbi-performance.html

"Every time DBI's prepare method is called, DBI parses the SQL command
looking for placeholder strings, and does some housekeeping work. Worse, a
context has to be built on the client and server sides of the connection
which the database will use to refer to the statement. These things take
time, and by eliminating these steps the time can be saved."

I have no idea what the real world penalty is for not using prepare_cached
in sqlgrey (or any) context, but for me it was either that or have a major
memory leak.  (In my DBI implementation, prepare_cached's cache didn't seem
to know about expiring old stuff, so the decision was easy..)  This is a
database and setup specific thing, so all I'm suggesting is that if you're
seeing a memory leak, consider the possibility of a faulty cache
implementation in your database library code.

I've had little success in finding information about prepare_cache's inner
workings..  how long cache elements stay valid, how they expire, how far it
will grow, or even what the expected performance gains might be, what (if
any) controls there are, how different databases are affected..  anybody
that could shed some light here, I'd appreciate it too!

Anyway, it did strike me that if the query changes, a different handle gets
returned, therefore any query that contains timestamp calculations outside
of a bound parameter will not benefit from the handle cache...

One thing I'll say for sure Dave, it works on a completely different level
from your memcache project (sql handle cache vs. data cache).  For a
specific sql statement, memcache could hold a few items already, and
prepare_cached could theoretically help a bit if new data is fetched from DB
using that same query.  So, they should co-exist happily!

Riaan