Dan,
I should have stated that the update would run faster by setting QUALCOLS + 10 ONLY if you were running QUALCOLS = 2 by default. Otherwise........ Set QUALCOLS 2 at the end of your command ONLY if you are normally running with QUALCOLS 2. Otherwise if your default is 10, do not set it to 2. It would make later mass updates run slower. The other option would be to add a computed column that does the string manipulation. You might find that to be more advantageous. Alter Table tmpdealerlocator + add column + Czipcode = (( SGET ( zipcode ,3,1)) + ' ' + ( SGET ( zipcode ,3,4))) Text 8 For this large of a table, if your scratch $$$ files (which hold your temp table) are not stored on the local drive, it would cause significant speed loss as well. Be sure your scratch files are set properly to the local drive. -Bob ----- Original Message ----- From: "Dan Goldberg" <dang@ lancecamper .com> To: "RBASE-L Mailing List" < rbase -l@ rbase .com> Sent: Wednesday, November 4, 2009 2:12:30 PM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Re: space in zipcode 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: rbase -l@ rbase .com [ mailto : rbase -l@ rbase .com] On Behalf Of ttc [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" <dang@ lancecamper .com> To: "RBASE-L Mailing List" < rbase -l@ rbase .com> 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
