[Fwd: Re: extra spaces]

"Denis A. Doroshenko" wrote:
>
> On Wed, Dec 20, 2000 at 10:35:01AM +1100, Mark Cowlishaw wrote:
> > > According to "Postgres: Introduction and Concepts", varchar is slower
> > > than char. So if you (like me) want to use char and get rid of the
> > > padding spaces, you may use a regex replacement, as in
> > >
> > > while (@row=$result->fetchrow)
> > > {
> > > $row[0] =~ s/[\s]+$//;
> > > }
> > >
> > > in perl, or
> > >
> > > $array["name"]=preg_replace("'[\s]+$'", "", $array["name"], -1);
> > >
> > > in PHP.
>
> i guess it would be better to use chop() (for trailing whitespaces) or
> even trim() (strips whitespaces off begining as well)... it should be
> considerably more effective then any regex...

I would guess that even though varchar or text is slower than char(),
it would still be raster than doing any of the above. Also, you
can push the function off to the backend using the SQL92 trim()
function.

If you use the SQL92 function, then you have the possibility of
incorporating the trim in a trigger so that every script gets
the benfit without requiring every programmer to remember it.

But, there is also the question of HOW MUCH slower a text or varchar
might be. I just created two tables with 262144 identical entries each.
The only difference was that one has a char(15) column and the other
has a text column. Then I timed (by wall clock) a series of selects
against each database. In order to ensure that network delays did not
affect the result, I just slected count and distinct. Request times
were between 7 and 15 seconds for each of the various queries,
depending on which query and whether the column was indexed.

The short answer to the question 'How much slower is text?' is 'not
measurably'. In fact, on my limited samples, the text edged of
char(15) mor often than the other way around (Actually, it was a
tie about half the time).

Now this is far from exact. I would expect a more detailed test
would in fact show char() to be slower. But based on this sample,
I would be rather surpised if it would be enough faster to offset
the cost of the scripted trims discussed above. And I would be
stunned to find that, except in isolated cases, that the performance
gain was worth the cost of added programming time.

Of course you may have one of those isolated cases. But I would
suggest starting with text. If you need the performance, you can
always replace the offending table at a later date. But the
programming time saved must have some value.

--
Karl DeBisschop kdebisschop(at)alert(dot)infoplease(dot)com
Learning Network/Information Please http://www.infoplease.com
Netsaint Plugin Developer kdebisschop(at)users(dot)sourceforge(dot)net