Dan, If zipcode is indexed, and If you don't have STATICDB on, you can also drop the index before the update, then rebuild it after. Rather than updating the index linked list 727,000 times, you'll be building it just once.
SET QUALCOLS 10 DROP INDEX zipcode IN tmpDealerLocator UPDATE tmpdealerlocator SET zipcode = + ((SGET(zipcode,3,1)) + ' ' + (SGET(zipcode,3,4))) CREATE INDEX xtmpZipCode ON tmpDealerLocator (Zipcode) SET QUALCOLS 2 Bill On Wed, Nov 4, 2009 at 3:12 PM, Dan Goldberg <[email protected]> wrote: > Thanks Bob and Buddy. I did both of those you recommended and it sped it > up but still takes alot of time. It is a big(727,000 rows) temporary table. > > Dan Goldberg > > ------------------------------ > *From:* [email protected] [mailto:[email protected]] *On Behalf Of * > [email protected] > *Sent:* Wednesday, November 04, 2009 6:30 AM > *To:* RBASE-L Mailing List > *Subject:* [RBASE-L] - Re: space in zipcode > > First, I would not use the WHERE clause. In this case, I do not believe > that it is accomplishing > > anything. If the record does not have a zip code, the update command is > not going to effect it > > anyway, Leaving out the where clause should increase speed somewhat. > > > > Based upon the last few emails, include this in front of your Update > Command... > > > > SET QUALCOLS 10 > > UPDATE tmpdealerlocator SET zipcode = + > ((SGET(zipcode,3,1)) + ' ' + (SGET(zipcode,3,4))) > > > > SET QUALCOLS 2 > > > > Based upon my recent test, this should make your update magnitudes faster. > > > > -Bob > > > ----- Original Message ----- > From: "Dan Goldberg" <[email protected]> > To: "RBASE-L Mailing List" <[email protected]> > Sent: Wednesday, November 4, 2009 8:18:11 AM GMT -06:00 US/Canada Central > Subject: [RBASE-L] - space in zipcode > > I have data that I receive from a vendor for canadian zipcodes and need to > put a space after the first three characters. > > V0R1R1 > > Need to be > > V0R 1R1 > > So I run the command: > > UPDATE tmpdealerlocator SET zipcode = + > ((SGET(zipcode,3,1)) + ' ' + (SGET(zipcode,3,4))) + > WHERE zipcode IS NOT NULL > > It runs buts takes forever to put the spaces in it. > > Does anyone have a suggestion to make it run faster?? > > TIA > > > > Dan Goldberg > > >
