At 02:26 PM 12/27/99 +0100, Karel Zak - Zakkr wrote:
>Sorry, but it is not good argument. If any routine (in the query path)
>spend time is not interesting write (other) fast routine? No, we must
>try rewrite this slowly part to faster version.
>
>*Very* simpl test over 10000 rows:
>
>$ time psql test -c "select date_part('second', d)
>from dtest;" -o /dev/null
>
>real 0m0.504s
>user 0m0.100s
>sys 0m0.000s
>
>$ time psql test -c "select to_char(d, 'SI') from
>dtest;" -o /dev/null
>
>real 0m0.288s
>user 0m0.100s
>sys 0m0.000s
This would seem to be a great argument to investigate why
date_part's so much slower. However, it says nothing about
the times saving of caching vs. not caching.
A more interesting comparison, more germane to the point under
discussion, would be:
time psql test -c "select d from dtest;"
In other words, how much overhead does "to_char" add? That's what
you need to look at if you want to measure whether or not caching's
worth it. Caching the parse of the format string will save a
percentage of the to_char overhead, but a test like the above
will at least help you get a handle on how much overhead the
format string parse adds.
>> Your caching code needs to guarantee that it can't leak memory
>> in any circumstance. In environments where database servers
>> run 24/7 that's far more important than minor increases in
>> speed.
>Yes, I agree - robus SQL is more importent, but always say "speed is not
>interesting, we can robus only" is way to robus-snail-SQL.
Which, of course, isn't what I said...after all, I've spent most of
my adult life writing highly optimizing compilers. I merely asked
if a typical query wouldn't swamp any savings that caching the
parse of a format string might yield.
>I want nice-robus-fast-SQL :-)
Sure, but given the great disparity between "date_part" and your
initial "to_char" implementation, more people might see a more
significant speed-up if you spent time speeding up "date_part"...
of course, if caching greatly speeds up queries using "to_char"
then it's probably worth doing, but at least try to measure first
before adding the complication.
At least, that's how I tend to work...
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.