>>>Nice try ;-)
>>>
>>>>>I expected it to be " where &tcKeyFld not in (select distinct &tcKeyFld ...).
>>>
>>>
>>>Unfortunately the use of expr is correct. The problem is that I need find records where the content has changed. The view does not, and can not, construct the keys before this routine as the existing records must keep the same key. If the key was somehow constructed from the contents then this method would be fine and probably very fast.
>>>
>>>The GetFldExpr returns a field list that can be brought into one string for comparison. Heres an example:
>>>
>>>padl( VALVEGROUP, 10 ) + padl( SIZEINC, 10 ) + padl( PORTS, 1 ) + SERIES + SIZE + PLUGDESIGN + LEAKAGE + SEAT + SEAT_CHAR + padl( SEATMAT_ID, 2 ) + PRATING + padl( PMA, 5 ) + padl( TMA, 5 ) + padl( TMO, 5 ) + padl( PMO, 5 ) + STEM_SEAL + STEM_CHAR + padl( PRODGPID, 10 ) + padl( RNGABLTY, 7 ) + padl( BODMATID, 2 ) + padl( CVALVE_MAT, 1 ) + UNIT_ID + padl( FAMILY_ID, 10 ) + padl( SGID, 10 )
>>>
>>>For each row, something like the following will be returned:
>>>
>>> 1.00 5 12KDN15 Caged parabolic0.01% of Kvs Hard faced W17PN40 40.0300.0300.0 40.0Bellows sealed B 0 50.00 44 0 0
>>>
>>>It is then possible to find what is one table and not the other using the select not in approach.
>>>
>>>Hold on. Just had bright idea!!!!
>>>
>>>index on expr tag expr and the use your seek method. Time to play ;-)
>>>
>>>I'll let you know
>>>
>>>
>>>Jason
>>
>>
Hi Jason,
>>Still "held on" :) I'm curious about the result.
>>Cetin
>
>
>Big, Huge, Magnificent Improvement!!!!
>
>If you remember, the original method took over an hour before I had to stop and get on with some work.
>
>Well this method takes about 15-20 minutes to complete. Its still fustrating as the indexes take so long to build on a million records and the scan also seems to crawl through the index, but hey it is a million records (about 25MB) of data. If they want quicker, they buy a bigger machine ;-)
Glad to hear that. But wait, 1 million records also seemed a lot to me before :) Now seeing the size is "only" 25 Mb I think it could be faster :) What about utilizing indiviual indexes instead of that one based on getexpr() ?
if seek(padl( m.VALVEGROUP, 10 ), "myTable", "myTag") ;
and seek(padl( m.SIZEINC, 10 ),"myTable", "myTag1") ;
...
endif
if seek(m.VALVEGROUP,"myTable","myTag") ;
and seek(m.SIZEINC),"myTable", "myTag1") ;
...
endif
VFP is kind enough to cut "if" testing on first failure. It's hard to say w/o testing. Building m. values for seek (if not directly saying table1.valvegroup is possible) seem to be the overheading (as far as I remember tcKeyField was a combined value). Yet it might be shorter than to create huge indexes. And indiviual indexes once built could stay there to make consequent updates ligthning fast :)
IMHO in any case seek() method would be faster and doesn't need the disk space as SQL method does :)
Cetin
