More efficient way than this? 1

[keepingbusy]

Hi

Can anyone suggest a quicker way to produce the below?

The code initially looks for a letter contained in a string within a field, removes that letter then replaces three other columns with infomation.

Your thoughts or suggestions would be appreciated as this current process takes about 35 minutes to search through 270,000 records.

Use MyTable

SCAN
  var1=UPCOLD
  DO CASE
    CASE "A" $ UPCOLD
      REPLACE UPCOLD WITH STRTRAN(var1,"A",'')
    CASE "B" $ UPCOLD
      REPLACE UPCOLD WITH STRTRAN(var1,"B",'')
    CASE "S" $ UPCOLD
      REPLACE UPCOLD WITH STRTRAN(var1,"S",'')
    CASE "L" $ UPCOLD
      REPLACE UPCOLD WITH STRTRAN(var1,"L",'')
    ENDCASE
    REPLACE COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg'
    REPLACE COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg'
    REPLACE COLUMN7 WITH ALLTRIM(UPCOLD)
ENDSCAN

Thank you

 

[Mike Lewis]

Several ideas come to mind.

First, are the four letters (A, B, S, L) mutually exclusive? In other words, if the string contains A, might it also contain B, S and L, and, if it does, do you also want to delete those other three letters.

If so, you could replace the entire CASE / ENDCASE construct with:

REPLACE UPCOLD WITH CHRTRAN(var1, "ABSL", "")

Going further, you could replace the SCAN / ENDSCAN loop with:

REPLACE ALL UPCOLD WITH CHRTRAN(var1, "ABSL", "")

I don't know how much quicker that would be, but it would be worth giving it a try.

Second suggestion is to combine the last three REPLACEs:

REPLACE COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
COLUMN7 WITH ALLTRIM(UPCOLD)

Again, take these outside the SCAN loop, and do a REPLACE ALL.

A single REPLACE of three fields is always faster than three separate statements, and a REPLACE ALL is generally faster than SCAN .... REPLACE .... ENDSCAN.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

Yes, I was going to suggest the same approach as Mike. If, however, the letters are mutually exclusive, you would need just 4 replaces with FOR "A" $ myField, FOR "B" $ myField, etc.

 

[keepingbusy]

Mike / Markros

if the string contains A, might it also contain B, S and L, and, if it does, do you also want to delete those other three letters.

The string will only ever contain one letter.

Second suggestion is to combine the last three REPLACEs:

Sounds good.

I'm just wondering if it would speed things up a bit if I perhaps used outside the scan/endscan:

REPLACE ALL UPCOLD WITH CHRTRAN(var1,"A","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"B","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"S","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"L","")

Mike's last suggestion (with the ALL command) of:

REPLACE [b]ALL[/b] COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN7 WITH ALLTRIM(UPCOLD)

makes sense.

I'll give these suggestions a go and post back.

Many thanks

 

[mmerlinn]

[ ]

Based on his code it looks like UPCOLD can only contain one of the letters in the string "ABSL", although it is possible that two or more could be present in that EXACT order. It is also possible that there are multiple occurrences of them, but only the one occurring first in the string will be replaced.

If only one can be present at any time, then the simple

[tt][blue]REPLACE UPCOLD WITH CHRTRAN(var1, "ABSL", "")[/blue][/tt]

as Mike suggested will work.

If more than one of the four is present, the code posted only changes the one nearest the beginning of UPCOLD and all others like it in the same string and probably cannot be easily condensed.

All of the above is assuming that the code presented works correctly in the first place.

Without more information I would at least do the following:
[tt][blue]
Use MyTable

SCAN
var1 = ALLTRIM(UPCOLD)

DO CASE
CASE "A" $ var1
var1 = STRTRAN(var1,"A",'')
CASE "B" $ var1
var1 = STRTRAN(var1,"B",'')
CASE "S" $ var1
var1 = STRTRAN(var1,"S",'')
CASE "L" $ var1
var1 = STRTRAN(var1,"L",'')
ENDCASE

REPLACE UPCOLD WITH var1, ;
COLUMN5 WITH var1 + '.jpg', ;
COLUMN6 WITH var1 + '.jpg', ;
COLUMN7 WITH var1

ENDSCAN
[/blue][/tt]

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[mmerlinn]

[ ]
Dang, should have waited 27 more seconds before posting. Would have had better information.

The string will only ever contain [red]one[/red] letter.

You have a problem if UPCOLD only contains one of those four letters and nothing else. The name of your jpg file would then be ".jpg" which is NOT a valid file name.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[keepingbusy]

mmerlinn

27 seconds can mean a lot in the world of Visual FoxPro which I'm sure, looking at your footer message, you already know about.

Regards

 

[mmerlinn]

[ ]

However, if UPCOLD contains other letters so that you have a valid file name in the end, then the following should work.

If EVERY record contains one of those letters, then just do

[tt][blue]
REPLACE ALL UPCOLD WITH CHRTRAN(var1, "ABSL", "")
REPLACE ALL COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
COLUMN7 WITH ALLTRIM(UPCOLD)
[/blue][/tt]

as suggested by Mike.

If only SOME records contain those letters, then this should work faster

[tt][blue]
SCAN
var1 = ALLTRIM(UPCOLD)
var2 = CHRTRAN(var1, "ABSL", '')

IF var1 <> var2
REPLACE UPCOLD WITH var2, ;
COLUMN5 WITH var2 + '.jpg', ;
COLUMN6 WITH var2 + '.jpg', ;
COLUMN7 WITH var2
ENDIF
ENDSCAN
[/blue][/tt]

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[keepingbusy]

Thank you mmerlinn (and Mike), I'll try these suggestions out over the next couple of days.

 

[Mike Lewis]

Be sure to let us know the result.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

I only have one thing to add.

Is there any chance that the field UPCOLD could be part of the default index for the table in question - in which case it would probably be a good idea to explicitly exclude it from
interfering with the processing of your scan/endscan..

use MyTable
Set order to
scan
...
endscan

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Hi keepingbusy,

REPLACE ALL UPCOLD WITH CHRTRAN(var1,"A","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"B","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"S","")
REPLACE ALL UPCOLD WITH CHRTRAN(var1,"L","")

This is a bad idea, as each replace starts from the value in var1, so you will get the same result, as if only doing the last REPLACE, only L's will be removed this way.

Why store UPCOLD in a var anyway?

REPLACE ALL UPCOLD WITH CHRTRAN(UPCOLD,'ABSL','') will remove any A,B,S or L within UPCOLD, if that is what you want. CHRTRAN doe not only replace the first letter found, it processes the whole string, like Strtran.

If you only want to replace one or the other letter really, like the CASE statement does, then you can't do that with REPLACE ALL, as you'd need to know which records already were processed with one of the CHRTRANS() beforehand. But I doubt you really wanted what you've written in the first place. Do you? In a CASE statement only the first CASE having a true condition is executed, so if A's are found, no B,S or L's are removed. Is that what you really want?

Bye, Olaf.

 

[Olaf Doschke]

To demonstrate how CASE does only execute the first true case:

Do Case 
   Case 'a' $ 'abc'
     MessageBox("first true case is done")
   Case 'b' $ 'abc'
     Messagebox("you'll never see this")
EndCase

Only the first messagebox is diplayed, even though also 'b' $ 'abc' is true.

I think you thought your initial code would run all CASES that are true. If you meant it that way and now are astonished about the second messagebox not appearing, you'll be fine with the simple REPLACE ALL UPCOLD WITH CHRTRAN(UPCOLD,'ABSL','') and then the second Replace of Column5,6 and 7 fields by Mike you already accepted.

Bye, Olaf.

 

[Imaginecorp]

You could do this like this as well... Dont know if it will be any faster, unless you create an index on deleted() in mytable...

Replace mtable.UPCOLD With Chrtran(mtable.UPCOLD, "ABSL", ""), ;
	mtable.COLUMN5 With Alltrim(mtable.UPCOLD)+'.jpg', ;
	mtable.COLUMN6 With Alltrim(mtable.UPCOLD)+'.jpg', ;
	mtable.COLUMN7 With Alltrim(mtable.UPCOLD)  ;
	FOR NOT DELETED("mytable") ;
	IN mytable

 

[brigmar]

If you're looking to remove ALL occurrences of A,B,S,L, then it can be done in ONE strtran/chrtran command as detailed already.

If you're looking to replicate the exact code you have (if A exists, get rid of it, but not B,S or L etc.,) then you can use the ICASE() function, which is to IIF() what CASE is to embedded IF/ELSE/ENDIFs.

Use MyTable
REPLACE UPCOLD  WITH ICASE( "A" $ UPCOLD, STRTRAN(UPCOLD,"A",') ;
                          , "B" $ UPCOLD, STRTRAN(UPCOLD,"B",') ;
                          , "S" $ UPCOLD, STRTRAN(UPCOLD,"S",') ;
                          , "L" $ UPCOLD, STRTRAN(UPCOLD,"L",') ;
                          , UPCOLD ) ;
      , COLUMN5 WITH ALLTRIM(UPCOLD)+".jpg" ;
      , COLUMN6 WITH ALLTRIM(UPCOLD)+".jpg" ;
      , COLUMN7 WITH UPCOLD ;
      ALL

 

[Mike Lewis]

Brigmar,

I was originally going to suggest using ICASE(). However, given that the occurrences of A, B, S, L are mutually-exclusive, it would probably be faster to use CHRTRAN(). In other words, you don't need to test to see which of the letters is present: you just remove them anyway.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Mike,

you're completely right, but we don't know anything about the data and how a JPG file name is composed, if it can have more than one of those letters in it at all etc. It's up to keepingbusy to decide whther that makes a difference or not.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

You're completely right as well. But I was relying on the following statement by KeepingBusy:

The string will only ever contain one letter.

On reflection, that doesn't sound right either. If it contains one letter, and you delete that letter, you would get an empty filename, which is surely not what he intended.

KeepingBusy, can you clarify that point? Can you give us some rules for what UPCOLD will contain?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Imaginecorp]

The code initially looks for a letter contained in a string within a field, removes that letter then replaces three other columns with infomation.

This tells me its Not a single letter....

The string will only ever contain one letter

this tells me only One "A / B /S /L" will be in the value of the field...

Lee; Have I read this right....

 

[brigmar]

Mike,
I prefaced the code with what you are saying; a single chrtran/strtran to remove all occurences as already covered could be what is required.
The ICASE code was to replicate the OP's original program logic if that was the particular behaviour needed.