Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

More efficient way than this? 1

Status
Not open for further replies.
keepingbusy

keepingbusy

Programmer
Apr 9, 2000
1,470
GB

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.
Code: 
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
 
Sort by date Sort by votes

imaginecorp (and the rest of the guys)

Sorry so many posts I'm lost!

In answer to your question:
this tells me only One "A / B /S /L" will be in the value of the field...
Click to expand...
You are right.

as an example the content of the field looks something like:

3246213456 A
4317890456 B
1287432908 A

In a nutshell, the data arrives in the above format, we remove the letter which leaves a set of numbers, which by the way, are UPC/SKU numbers.

The reason for the removal is that the barcode scanner does not recognise the field data with a letter once imported into the table.

Hope that helps

Lee


 
Upvote 0 Downvote
[ ]

keepingbusy

With your new information, I realized my last code will not work for you unless you add this just before the REPLACE:
[tt][blue]
var2 = ALLTRIM(var2)
[/blue][/tt]

mmerlinn


"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
 
Upvote 0 Downvote
Lee; try the single replace statement with an Index on deleted(), I am sure it will be faster than the scan and multiple replaces...
 
Upvote 0 Downvote
Lee,

OK, in that case, I would revert to my original suggestion:

Code: 
REPLACE ALL UPCOLD WITH CHRTRAN(UPCOLD, "ABSL", "")
REPLACE ALL COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN7 WITH ALLTRIM(UPCOLD)

However, now that we know the letter is always at the end of the string, and if there are always 10 characters and a space before it, then this will be quicker:

Code: 
REPLACE ALL UPCOLD WITH LEFT(UPCOLD, 11)
REPLACE ALL COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN7 WITH ALLTRIM(UPCOLD)

In fact, we can probably improve it slightly more, but perhaps you could try this and see if it makes a difference.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 
Upvote 0 Downvote
Another thought .... If the code is always exactly ten digits and a space and a letter, you can get rid of the ALLTRIMs.

In other words:

Code: 
REPLACE ALL UPCOLD WITH LEFT(UPCOLD, 11)
REPLACE ALL COLUMN5 WITH UPCOLD+'.jpg', ;
  COLUMN6 WITH UPCOLD+'.jpg', ;
  COLUMN7 WITH UPCOLD

That's because the LEFT() will remove that final space.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 
Upvote 0 Downvote
Lee,

If the field always looks like this:
3246213456 A
4317890456 B
1287432908 A,
meaning, it always has 10 digits, space, and then a letter - of which you need to get rid - here is another thought:
Code: 
[blue]REPLACE ALL UPCOLD WITH PADL(VAL(UPCOLD),10,"0")+" "[/blue]
[COLOR=black gray]*!* I used PADL here instead of STR() just in case you have leading zeros somewhere[/color]
REPLACE ALL COLUMN5 WITH UPCOLD+'.jpg', ;
  COLUMN6 WITH UPCOLD+'.jpg', ;
  COLUMN7 WITH UPCOLD

On the other hand, you say, "The reason for the removal is that the barcode scanner does not recognise the field data with a letter once imported into the table." In what format do you get your data? You don't have to import that letter into your table in the first place. (In many cases, you could load the letter into a separate column of a cursor, then leave it out when selecting or appending into your table.)

 
Upvote 0 Downvote

Hello guys, perhaps I have thrown a spanner in the works here:
as an example the content of the field looks something like:
Click to expand...
This was merely an example as most of you are aware, UPC/SKU numbers can be anything from 10 to 14 digits. Then of course, the way we receive them is a string of numbers and a letter which is only an identifier from where it orginated which is not important in this scenario other than we need to remove it.

I'm going to give some of your suggestions a go in the next day or two so I will post back with a result.

I am grateful to all those who have posted on this thread, an amazing response.

Sincerest thanks all

Lee


 
Upvote 0 Downvote
My instinct would be to write it in a single replace statement, saving a pass through the table:

Code: 
REPLACE UPCOLD  WITH GETWORDNUM(UPCOLD,1) ;
      , COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg' ;
      , COLUMN6 WITH COLUMN5 ;
      , COLUMN7 WITH UPCOLD ;
      ALL
(Using GETWORDNUM to extract the variable length UPC/SKU)

 
Upvote 0 Downvote
Brigmar has it, it's simply getwordnum(upcold,1) then.
You could also filter for digits only by chrtran(chrtran(upcold,'0123456789',''),'').

On flaw in Mikes last answer, if you remove the right part via Left(upcold,11) you don't shorte the upcold field length, only if it's varchar upcold+'.jpg' would give the right picture file name, otherwise you'dstill need to trim, at least RTRIM(upcold)+'.jpg'.

Lesson to Lee: come up with the real life problem, that was much easier to solve, than to puzzle if the nature of the case statement matters or not.

Bye, Olaf.
 
Upvote 0 Downvote
Olaf,

You're right when you say that, with my use of LEFT(), you would still need to RTRIM(). But, given that I was assuming that the code consisted of ten letters plus a space plus a digit, I could have used LEFT(UPCOLD,10) instead of LEFT(UPCOLD,11) and avoided putting an RTRIM() into the stew.

But, in any case, Lee is now saying that the code can consist of any number of characters.If the letter is always at the end, I would still use LEFT(), but would do LEFT(UPCODE), LEN(UPCODE)-1). If the letter can be anywhere in the string, I would revert to my previous suggesion of using CHRTRAN().

I would also be caution of using GETWORDNUM(). My guess is that this would be slower than LEFT() or CHRTRAN(). But that can be tested.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 
Upvote 0 Downvote
Hello Mike,

well, no Mike, you still the same error about the length of a char field LEFT(UPCODE), LEN(UPCODE)-1) wouldn't necessarily remove the letter, because Len(upcode) is the length of the upcode field, not the length of the trimmed text within the upcode field.

Think of a C(N) field, the letter isn't alwas at Nth position. With the varying length of the numbers its anywhere within the last 4 or 5 chars of that field. You'd need LEFT(Upcode, Len(RTRIM(Upcode))-1) to make sure you cut off the last char, the letter.

For the simplicity and maintainability and readability of the code I'd rather use Getwordnum(Upcode,1). It is clearly visible what it does and it's fast enough.

Bye, Olaf.
 
Upvote 0 Downvote
On more sample for Mike:

If for the moment we assume it's all only 10 digit numbers and upcode is a C(12) field with 2 extra chars for space and a letter, then taking you code with your own correction of 10 instead of 11 in case of numbers with length of 10 digits this still isn't correct:

Code: 
REPLACE ALL UPCOLD WITH LEFT(UPCOLD, 10)

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

As upcode is C(12), you'd change '0123456789 A' to '0123456789 ' via setting upcode to Left(upcold,10), you don'T set upcode to '0123456789', because upcode is not a string var it's a C(12) field.

Left(upcode,10) is '0123456789' but if you set a C(12) field to '0123456789' it's '0123456789 ', not '0123456789'.

So upcode+'.jpg' then is '0123456789 .jpg', not '0123456789.jpg' as needed. See now why you still need rtrim, no matter if you did Left(upcode,11) or Left(upcode,10)?

Bye, Olaf.

 
Upvote 0 Downvote
Olaf,

Ah, you mean you have to do RTRIM() when you do REPLACE ALL COLUMN5 WITH UPCOLD+'.jpg'. In other words:

Code: 
REPLACE ALL COLUMN5 WITH RTIM(UPCOLD)+'.jpg'

Point taken. But it doesn't invalidate my main point, which is to use LEFT() if the letter is always at the end, or CHRTRAN() otherwise.

I feel that would be faster than GETWORDNUM(). It might or might be less readable, but the brief was to improve performance. It will be interesting to see what timings Lee comes up with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 
Upvote 0 Downvote
I keep coming back to this, but why is everybody using 2 (or more) REPLACE statements in their solutions with performance being the key?
 
Upvote 0 Downvote
Brigmar,

I can't answer for anyone else, but I am using two REPLACEs (no more than two) because the first one is replacing the field UPCODE, and the others are placing the new value of UPCODE into other fields.

It might well be perfectly OK to do that, but I would want to test it before I was sure.

That said, your point is well taken. As I said right at the beginning, a single REPLACE with multiple clauses will always be faster than multiple REPLACEs. This is especially true when you do a REPLACE ALL in a large table.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

Hi

Having run a few tests, we finally used the following:
Code: 
REPLACE ALL UPCOLD WITH CHRTRAN(UPCOLD, "ABSL", "")
REPLACE ALL COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN6 WITH ALLTRIM(UPCOLD)+'.jpg', ;
  COLUMN7 WITH ALLTRIM(UPCOLD)
This dramitically reduced the amount of time it took to process the table. I cant give you an exact time as the process runs anything from 100,000 to over 250,000 records.

Thanks to all who posted on this thread.

Lee
 
Upvote 0 Downvote
The REPLACE command, unlike an UPDATE statement, is actually performed in the order in which it is written. So to increase performance another 10-15% you could use the following (modified from the examples given in this thread)...

Code: 
REPLACE ALL UPCOLD WITH CHRTRAN(UPCOLD, "ABSL", ""), ;
COLUMN5 WITH ALLTRIM(UPCOLD)+'.jpg', ;
COLUMN6 WITH COLUMN5, ;
COLUMN7 WITH ALLTRIM(UPCOLD)

boyd.gif

SweetPotato Software Website
My Blog
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

german12
  • Locked
  • Question
How to filter a list like this sample 3
Replies
2
Views
137
Chris Miller
Chris Miller
Chris Miller
  • Locked
  • Helpful tip
A way to have GetAll() by using SetAll() 1
Replies
6
Views
137
Chris Miller
Chris Miller
Filip Brnic
  • Locked
  • Question
THE DATA SOURCE FOR THIS OBJECT MUST BE A VARIABLE REFERENCE. Help me :D 2
Replies
12
Views
210
Chris Miller
Chris Miller
Steve-vfp9user
  • Locked
  • Question
SQL statement, is there a quicker way? 6
2
Replies
20
Views
311
Steve-vfp9user
Steve-vfp9user
Rajesh Karunakaran
  • Locked
  • Question
What would be good way to export a report (FRX) as an image (eg PNG) 3
Replies
9
Views
188
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top