Skip site navigation (1) Skip section navigation (2)

Re: String Translation

From: Dror Matalon <dror(at)zapatec(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: String Translation
Date: 2003-10-16 18:01:11
Message-ID: 20031016180111.GY2979@rlx11.zapatec.com (view raw or flat)
Thread:
Lists: sfpug
Hey David,

I would try the standard tuning things:

1. Run VACUUM ANALYZE on the table.
2. Run reindex on the table
2. Print out the query and then do a explain analyze of the query in
psql and see how long it takes, and if so why.

If it's still slow, provide the explain analyze here, and we can take a
look at it.

Dror

On Wed, Oct 15, 2003 at 05:21:14PM -0700, David Wheeler wrote:
> Hi All,
> 
> I have an upgrade script for the forthcoming version of Bricolage. It 
> has a function that looks like this:
> 
> sub update_all {
>     my ($table) = @_;
>     my $get_name     = prepare("SELECT name FROM $table");
>     my $set_key_name = prepare("UPDATE $table SET key_name=? WHERE 
> name=?");
> 
>     my $name;
>     execute($get_name);
>     bind_columns($get_name, \$name);
> 
>     while (fetch($get_name)) {
>         my $key_name = lc($name);
>         $key_name =~ y/a-z0-9/_/cs;
>         execute($set_key_name, $key_name, $name);
>     }
> }
> 
> Now, this is all well and good, except that it takes _forever_ to run! 
> I have it running now on a database with two tables to be updated, with 
> a total of 2213 rows, and it has been running for about 30 mins. And 
> yes, it's a fast server with a reasonably tuned database.
> 
> Now, I could speed this up if it could just run in a single query, 
> rather than with a big select and 2213 UPDATEs. The trouble is, I don't 
> know of an SQL equivalent to y/a-z0-9/_/cs, which translates any non 
> alphanumeric characters to underscores. I see that there's a 
> substring() function that supports POSIX regular expressions, but that 
> doesn't do a substitution.
> 
> So my question is, does anyone know of a way I could do this in the 
> database (and it is a one-time change [I hope!])? And if there is one, 
> will it be faster?
> 
> Many TIA,
> 
> David
> 
> -- 
> David Wheeler                                     AIM: dwTheory
> david(at)kineticode(dot)com                              ICQ: 15726394
> http://www.kineticode.com/                     Yahoo!: dew7e
>                                                Jabber: Theory(at)jabber(dot)org
> Kineticode. Setting knowledge in motion.[sm]
> 

-- 
Dror Matalon
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com

In response to

Responses

sfpug by date

Next:From: David WheelerDate: 2003-10-16 18:03:34
Subject: Re: String Translation
Previous:From: Sean ChittendenDate: 2003-10-16 17:59:59
Subject: Re: String Translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group