CALC MIN(VAL)...FOR... I need to speed this process up

[Bowne]

The code below works but I would like to speed it up. Any ideas? Table1 has 1.1 million records. Table2 was created using: SELECT COUNT(*), * FROM table1...GROUP BY address, zip and has 53k records.

findaddr = ""
findzip = ""
lowest = 0

use table1 in 0
use table2 in 0

select table2
go top

do while not eof()
     * save the values in table 2
     findaddr = address
     findzip = zip

     * get the min value in table1.oldseq
     select table1
     calculate min(val(oldseq)) for (address == findaddr and zip == findzip) to lowest

     * save that value (as char) in table2.lowseq
     select distaddr
     replace lowseq with alltrim(str(lowest)) 
    
     skip 1
enddo

 

[ramani]

HI

SELECT table2

** INDEX on address+zip if not already done .............. as a compound index (myIndex)

SET ORDER TO myIndex

SELECT address, zip, MIN(VAL(oldseq)) AS lowseq ;
FROM table1 ;
INTO CURSOR myTemp GROUP BY address, zip
** If Table1 has index on address, zip the SQL will go fast.

SET RELATION TO address+zip INTO table2
REPLACE ALL table2.lowseq WITH lowseq

Hope this helps. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[alank01]

just as a suggestion to ramani's tip becuase of the way the min function will work on the ascii field you could leave out the val() function and gain some more optimization from the tag and not have to convert lowseq back to str.

eg '00030' will give you 30 in lowseq.

 

[fluteplr]

BTW
I totally agree with the above solutions, however one other note.


Your construct of:

do while not eof()
skip 1
enddo

was replace by
scan
endscan
about 10 versions ago.

In any code scan endscan is about 10 times faster than a do while.

 

[TomasDill]

Hi!

In any code scan endscan is about 10 times faster than a do while

fluteplr, I do not agree with you here. Both ways are similar unless you're using filtering. In such case SCAN ... FOR is really faster because it can use the optimization. When scanning all records, there are no difference. Anyway, SCAN looks better





Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[TomasDill]

Bowne, instead of that loop, try following:

SELECT t2.address, t2.zip, min(val(t1.oldseq)) as lowest from table2 t2 left join t1 on t2.address=t1.address AND t2.zip=t1.zip
group by t2.address, t2.zip
into cursor TempCursor

select 0
use (dbf('TempCursor')) again alias TC
index on address + zip tag AddrZip
set index to
use in TempCursor

select table2
SCAN ALL
seek table2.address+table2.zip ORDER TAG AddrZip IN TC
replace table2.lowseq with alltrim(str(TC.lowest))
ENDSCAN

I also wondered, why you could not do all above in a single select?

SELECT COUNT(*), min(val(oldseq)) as lowest FROM table1 ... GROUP BY address, zip

Finally,
SELECT COUNT(*), * FROM table1...GROUP BY address, zip

makes no sense, because * will return all fields from which record? Say, you have 5 records in group (with the same address and zip). Which of these 5 records are returned from the query? Look at this more and think more. even query like
SELECT min(val(oldseq)) as lowest, * FROM table1 ... GROUP BY address, zip
does not quarantee that * will return values from record where min valu found. It require another approach.



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs