Hi Naomi,

had a few minutes to brush up the eample. It shows that
the Cartesien joins run time only depends on record count in base tables
the selectivity inherent in the filtering can be harnassed via rushmore (higher selectivity>>shorter time)
even on moderate data sizes the speedups can range up to 1000 depending on the selectivity.

Let it run overnight after setting lnStop and lnMulti to settings appropriate for your machine to be busy for a few H <g>.

regards

thomas

CLEAR
CLEAR all
SYS(3054, 0)
PRIVATE pnLeftSignif, pnStart
LOCAL lnRows, lnStop, lnMulti
lnStop	= 100
lnMulti	= 1000
FOR lnRows = 1 TO m.lnStop
	FOR pnLeftSignif = 4 TO 7
		= OneRun(m.lnRows * m.lnMulti)
	NEXT
next

FUNCTION OneRUn
PARAMETERS tnRowFill
?
CREATE CURSOR od (pk I, nn C(10), ad c(10))
INDEX on pk TAG pk candidate

LOCAL lnRun
FOR lnRUn = 1 TO m.tnRowFill
	lca = + PADR(ALLTRIM(STR(m.lnRUn)), 8, "!")
	INSERT INTO od VALUES (RECCOUNT() + 1, "Ac" + m.lca, "B1" + m.lca)
	INSERT INTO od VALUES (RECCOUNT() + 1, "Aa" + m.lca, "A1" + m.lca)
	INSERT INTO od VALUES (RECCOUNT() + 1, "Ba" + m.lca, "C1" + m.lca)
	INSERT INTO od VALUES (RECCOUNT() + 1, "Da" + m.lca, "D1" + m.lca)
next
INDEX on LEFT(nn, m.pnLeftSignif) TAG nn1
INDEX on LEFT(ad, m.pnLeftSignif) TAG ad1
SET ORDER TO 

CREATE CURSOR nd (pk I, nn C(15), ad c(15))
INDEX on pk TAG pk candidate
FOR lnRUn = 1 TO m.tnRowFill
	lca = + PADR(ALLTRIM(STR(m.lnRUn)), 8, "!")
	INSERT INTO nd VALUES (RECCOUNT() + 1, "Xa" + m.lca, "D1" + m.lca)
	INSERT INTO nd VALUES (RECCOUNT() + 1, "Aa" + m.lca, "Z1" + m.lca)
Next
INDEX on LEFT(nn, m.pnLeftSignif) TAG nn1
INDEX on LEFT(ad, m.pnLeftSignif) TAG ad1
SET ORDER TO 

pnStart = SECONDS()
SELECT ;
		  od.pk as pko, od.nn as nno, od.ad as ado ;
		, nd.pk as pkn, nd.nn as nnn, nd.ad as adn;
	FROM od ;
	JOIN nd ;
		ON LEFT(od.nn, m.pnLeftSignif) == LEFT(nd.nn, m.pnLeftSignif) ;
		OR LEFT(od.ad, m.pnLeftSignif) == LEFT(nd.ad, m.pnLeftSignif) ;
	INTO CURSOR t_Cart

= ShowTime()

? ">>Now fast for large tables!"
= Sel_2Step(.t., "ManCdx")
? ">>vfp can create the temp index if lead by fomulating 2 Selects!"
= Sel_2Step(.f., "TmpCdx")

FUNCTION Sel_2Step(tlCdxManual, tcCursName)
pnStart = SECONDS()

SELECT ;
		  od.pk as pko, od.nn as nno, od.ad as ado ;
		, nd.pk as pkn, nd.nn as nnn, nd.ad as adn ;
	FROM od ;
	JOIN nd ;
		ON LEFT(od.nn, m.pnLeftSignif) == LEFT(nd.nn, m.pnLeftSignif) ;
	INTO CURSOR &tcCursName ReadWrite

IF tlCdxManual
	INDEX on PADL(ALLTRIM(STR(pko)), 9)  ;
		   + PADL(ALLTRIM(STR(pkn)), 9) TAG pk_both
	SET ORDER TO 
endif

INSERT INTO &tcCursName ;
	SELECT ;
			  od.pk as pko, od.nn as nno, od.ad as ado ;
			, nd.pk as pkn, nd.nn as nnn, nd.ad as adn ;
		FROM od ;
		JOIN nd ;
			ON LEFT(od.ad, m.pnLeftSignif) == LEFT(nd.ad, m.pnLeftSignif) ;
		WHERE NOT ;
			  PADL(ALLTRIM(STR(od.pk)), 9) ;
			+ PADL(ALLTRIM(STR(nd.pk)), 9) ;
		in ( SELECT ;
					PADL(ALLTRIM(STR(pko)), 9) + ;
				   	PADL(ALLTRIM(STR(pkn)), 9) ;
			   FROM &tcCursName ) 
= ShowTime()

FUNCTION ShowTime()
LOCAL lcMess
lcMess =  " Secs: " 	+ STR(SECONDS() - m.pnStart, 15,2) ;
		+ " Signif: " 	+ STR(m.pnLeftSignif) ;
		+ " Fills: "	+ STR(m.tnRowFill) ;
		+ " Result: "	+ STR(RECCOUNT()) ;
		+ " Alias: "	+ ALIAS()
? m.lcMess
= STRTOFILE(m.lcMess + CHR(13), "Prot.txt", .t.)