More efficient / speedier way to do this? 8

[keepingbusy]

Hi

We have a process that carries out the following:

There are two tables that store information that has been imported from txt files. One table contains current information (Today) and the other contains previous information (Yesterday).

On a daily basis, new information is imported into the current table and the current tables data is sent to yesterdays table depending on conditions.

These conditions include a match on a unique number (called an SKU number) and also whether or not the price is < the previous one.

We have some code below which does the job perfectly and when you process about 68000 records in the tables, it takes about 30 minutes.

However, when the table increase to over 200,000 we are now talking many hours to process (this is done overnight but not really the best way to do it).

I am looking for some suggestions (not the sollution) on how to speed this up.

To try and simplify things, TODAY stores the SELL price and SKU number, it then looks for it in TPREVIOUS (Yesterdays data) and if it finds a match, deletes the record. Here is what we have so far:

USE TODAY  && CURRENT / NEW LIST
GO TOP
	
DO WHILE NOT EOF()
  mrecno=0
  mrecno=RECNO()
		
  STORE " "		TO mdelete
  STORE SPACE(15)	TO mcolumn5
  STORE SPACE(16)	TO mcolumn6
  STORE SELL		TO mcolumn5
  STORE SKU		TO mcolumn6
	
* NOW START A SEARCH TO SEE IF TODAY SKU NUMBER
* AND PRICE MATCH THAT OF YESTERDAYS
* IF IT DOES, DELETE IT FROM CURRENT

  USE TPREVIOUS
  GO TOP
		
  LOCATE FOR ALLTRIM(COLUMN5)=ALLTRIM(mcolumn5) ;
    AND ALLTRIM(COLUMN6)=ALLTRIM(mcolumn6)

* IF WE FIND A MATCH IN THE CURRENT TABLE
* THAT WAS IN YESTERDAYS, DELETE IT

  IF FOUND()
    REPLACE DELETEIT WITH "Y"
    STORE "Y" TO mdelete
  ENDI
		
  CLOSE DATABASES
		
  USE TODAY SHARED
  GO mrecno
  IF mdelete="Y"
    REPLACE DELETEIT WITH "Y"
  ENDI
  SKIP
ENDDO

I would appreciate your thoughts on this.

Many thanks

 

[Olaf Doschke]

I see something else mising: ALL

USE TPrevious IN 0 ORDER SKUSELL && index on SKU+SELL TAG SKUSELL must exist
USE Today In 0
Select Today
Set Relation TO SKU+SELL INTO TPrevious
Replace [COLOR=red]ALL[/color] TPrevious.DeleteIt With "Y", Today.DeleteIt WITH "Y" FOR !EOF("TPrevious")

Bye, Olaf.

 

[TamarGranor]

Olaf -

You definitely don't need the ALL. When you have a FOR clause in a scoped command, ALL is implied and then modified by the FOR clause.

I do understand what you're doing and I guess in this case, replacing across two work areas is a reasonable solution because you're depending on the relation between them. I would still use the IN clause to ensure that you're operating in the expected work area. There are too many ways that the work area can get changed behind the scenes and really mess you up.

Tamar

 

[Olaf Doschke]

Okay Tamar,

then it's my false assumption that the IN clause will only determine to what workarea replacements are scoped.

So you still can replace in multiple work areas using an IN clause. I should have tried that earlier.

On the other side I'd also apply the ALL, even if a FOR clause forces this scope anyway for the same reason you'd suggest an IN, I'd suggest this for making clearer, that it's not only a single record replacement.

Bye, Olaf.

 

[Olaf Doschke]

no ALL is a scope, it does only mean ALL if you don't filter it with a FOR. FOR is just a filter and as the default scope is NEXT 1 you could read REPLACE ... FOR condition, theat the current record is only replaced, if the condition is met and otherwise not. And this was my understanding before I tried.

I'm still for clarity, replace ALL... FOR condition makes it clear, that it's not for a single record.

Bye, Olaf.