Hi Naomi,

I put together a small sample where a cartesion join is split into 2 distinct SQL statements each helped by rushmore. Feel free to add a bit of code to fill the tables to capacity and time with a few millions of rows - but the discrimination factor was MUCH higher thän left(,1) - we had a selectivity on the order of 10**7 and 10**8 reccount() for od and nd and 5*10**6 reccount() for the result sets<g>.

So get all fast joins in your app together in 1 Statement and try alternative approaches to eliminate the cartesian join in a second one - cutting it down to 2 tables/cursors for the last "join" - operation helps as the code grows.

Sometimes I consider writing a small showplan enhancer to get better data...

clea
CLEAR all
CREATE CURSOR od (pk I, nn C(5), ad c(5))
INSERT INTO od VALUES (1, "Ac", "B1")
INSERT INTO od VALUES (2, "Aa", "A1")
INSERT INTO od VALUES (3, "Ba", "C1")
INSERT INTO od VALUES (4, "Da", "D1")
INDEX on LEFT(nn, 1) TAG nn1
INDEX on LEFT(ad, 1) TAG ad1
SET ORDER TO 

CREATE CURSOR nd (pk I, nn C(5), ad c(5))
INSERT INTO nd VALUES (1, "Xa", "D1")
INSERT INTO nd VALUES (2, "Aa", "Z1")
INDEX on LEFT(nn, 1) TAG nn1
INDEX on LEFT(ad, 1) TAG ad1
SET ORDER TO 

SYS(3054, 12)
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, 1) == LEFT(nd.nn, 1) ;
		OR LEFT(od.ad, 1) == LEFT(nd.ad, 1) ;
	INTO CURSOR t_Cart
?
? ">>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)
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, 1) == LEFT(nd.nn, 1) ;
	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, 1) == LEFT(nd.ad, 1) ;
		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 )

regards

thomas