Yes, I assume it will have to sort the columns, to combine them.

Do you mean there would be a more efficient algorithm for Visual FoxPro to obtain the same result?


I do not say that to write a RELATIONAL INVARIANT OPTIMIZER it is a simple task.

I only say that what currently VFP SQL Engine do now,it can be made better.

Considering that a program XBASE is not comparable ( on speed measure ) with a C++ equivalent,
I leave you the final judgment, after to have executed the following code with attention.

CLEAR
CLOSE DATABASES ALL
CLOSE TABLES ALL
SET TALK OFF
SET ESCAPE ON
SYS(3054,0)
?' THE SCENARY : A MASTER,CHILD1,CHILD2 TABLES WITH A GROUPING FIELD INTO THE MASTER'

CREATE CURSOR master (F1 I)

CREATE CURSOR child1 (F1 i,F2 I)
CREATE CURSOR child2 (F1 i,F2 I)

*#DEFINE TESTFULLJOIN 0 && uncomment this line for test FULL JOIN
#IFDEF TESTFULLJOIN
	#DEFINE DOMAINSIZE			70
	#define AVERAGEGROUP1SIZE	500
	#define AVERAGEGROUP2SIZE	50
#ELSE
	#DEFINE DOMAINSIZE			100
	#define AVERAGEGROUP1SIZE	10000
	#define AVERAGEGROUP2SIZE	200
#ENDIF
FOR k=1 TO DOMAINSIZE
	INSERT INTO master VALUES (m.k)
	FOR J=1 TO 2*AVERAGEGROUP1SIZE*RAND()
		INSERT INTO child1 VALUES(m.k,2^20*(RAND()-0.5))
	NEXT
	FOR J=1 TO 2*AVERAGEGROUP2SIZE*RAND()
		INSERT INTO child2 VALUES(m.k,2^20*(RAND()-0.5))
	NEXT	
NEXT
SELECT CHILD1
INDEX ON F1 TAG TAGF1
INDEX ON BINTOC(F1)+BINTOC(F2) TAG TAGF1F2

SELECT CHILD2
INDEX ON F1 TAG TAGF1
INDEX ON BINTOC(F1)+BINTOC(F2) TAG TAGF1F2

*********************************
* burnin
SELECT F1,SUM(F2) sumF2 FROM child1 GROUP BY 1 INTO CURSOR rSum 
USE

? ' SUM **********'
t1=SECONDS()
SELECT F1,SUM(F2) sumF2 FROM child1 GROUP BY 1 INTO CURSOR rSum 
? SECONDS()-t1,_TALLY,'VFP ENGINE SUM GROUPS'

t1=SECONDS()
CREATE CURSOR rSum1(F1 I,sumF2 I)
SELECT 0
USE DBF('child1') AGAIN ALIAS WORKALIAS ORDER tagf1
DO WHILE !EOF()
	STORE F1 TO grpF1
	CALCULATE WHILE F1=m.grpF1 SUM(F2) TO sumF2
	INSERT INTO rSum1 VALUES (m.grpF1,M.sumF2)
ENDDO
USE
SELECT rSum1
GO TOP
? SECONDS()-t1,RECCOUNT(),'XBASE SUM WITH ORDERED GROUP CYCLE'

? ' COUNT *********'
t1=SECONDS()
SELECT F1,COUNT(F2) cntF2 FROM child1 GROUP BY 1 INTO CURSOR rCount
? SECONDS()-t1,_TALLY,'VFP ENGINE COUNT GROUPS'

t1=SECONDS()
CREATE CURSOR rCount2 (F1 I,cntF2 I)
SELECT 0
USE DBF('child1') AGAIN ALIAS WORKALIAS ORDER tagf1
SCAN
	SET KEY TO F1
	COUNT
	GO BOTTOM
	INSERT INTO rCount2 VALUES (WORKALIAS.F1,_TALLY)
	SET KEY TO
ENDSCAN
USE
SELECT rCount2
GO TOP
? SECONDS()-t1,RECCOUNT(),'(best) XBASE COUNT WITHIN A KEY'

? 'MIN MAX ********'
t1=SECONDS()
SELECT F1,MIN(F2) minF2,MAX(F2) MaxF2 FROM child1 GROUP BY 1 INTO CURSOR rMinMax
? SECONDS()-t1,RECCOUNT(),'VFP ENGINE MIN MAX GROUPS'

t1=SECONDS()
CREATE CURSOR rMinMax1(F1 I,minF2 I,maxF2 I)
SELECT 0
USE DBF('child1') AGAIN ALIAS WORKALIAS ORDER tagf1
DO WHILE !EOF()
	STORE F1 TO grpF1
	CALCULATE WHILE F1=m.grpF1 MIN(F2),MAX(F2) TO minF2,maxF2
	INSERT INTO rMinMax1 VALUES (m.grpF1,m.minF2,m.maxF2)
ENDDO
USE
SELECT rMinMax1
GO TOP
? SECONDS()-t1,RECCOUNT(),'XBASE MIN MAX  WITH ORDERED GROUP CYCLE'

t1=SECONDS()
CREATE CURSOR rMinMax2(F1 I,minF2 I,maxF2 I)
SELECT 0
USE DBF('child1') AGAIN ALIAS WORKALIAS ORDER TAGF1F2
SCAN
	SET KEY TO BINTOC(F1)
	INSERT INTO rMinMax2 VALUES (WORKALIAS.F1,WORKALIAS.F2,0)
	GO BOTTOM
	REPLACE rMinMax2.maxF2 WITH F2
	SET KEY TO
ENDSCAN
USE
SELECT rMinMax2
GO TOP
? SECONDS()-t1,RECCOUNT(),'(best) XBASE MIN MAX WITHIN A KEY'

? '******* MORE COMPLEX SCENARIO: AGGREGATE RESULT FROM CHILDS TABLES'

*SYS(3054,11)
? 'FULL JOIN GROUPING generate a temp table with on average:',DOMAINSIZE*AVERAGEGROUP1SIZE*AVERAGEGROUP2SIZE,' Records'
IF DOMAINSIZE*AVERAGEGROUP1SIZE*AVERAGEGROUP2SIZE>5000000
	?? 'SKIPPED'
ELSE
	WAIT WINDOWS "wailt please..." NOWAIT NOCLEAR
	t1=SECONDS()
		SELECT NVL(child1.F1,child2.F1) F1,MIN(child1.F2) min1F2,MIN(child2.F2) min2F2;
		INTO CURSOR R2minmin8;
		FROM child1	FULL JOIN child2 ON child1.F1=child2.F1 GROUP BY 1

	? SECONDS()-t1,RECCOUNT(),'VFP8 ENGINE FULL JOIN TWO TABLES MIN'
	WAIT CLEAR
ENDIF
t1=SECONDS()
	USE DBF('child1') IN 0 AGAIN ALIAS LOOKUP1 ORDER TAGF1F2
	USE DBF('child2') IN 0 AGAIN ALIAS LOOKUP2 ORDER TAGF1F2
	SELECT	F1	;
		,	LOOKUP(LOOKUP1.F2,BINTOC(F1),LOOKUP1.F1,2) min1F2	;
		,	LOOKUP(LOOKUP2.F2,BINTOC(F1),LOOKUP2.F1,2) min2F2	;
	INTO CURSOR R2minmin8_XBASE	;
	FROM MASTER
	USE IN LOOKUP1
	USE IN LOOKUP2
? SECONDS()-t1,RECCOUNT(),'(best) VFP8 ENGINE MASTER LOOP LOOKUP KEY TWO TABLES MIN'
	
IF VERSION(5)=900
	
	t1=SECONDS()
	SELECT F1	;
		,(SELECT MIN(F2) FROM child1 WHERE F1=MASTER.F1) min1F2	;
		,(SELECT MIN(F2) FROM child2 WHERE F1=MASTER.F1) min2F2	;
	INTO CURSOR R2minmin9_2	;
	FROM (SELECT F1 FROM child1 UNION SELECT F1 FROM child2) MASTER
	
	? SECONDS()-t1,RECCOUNT(),'VFP9 ENGINE (UNION) SUBQUERY TWO TABLES MIN'

* NEXT IS THE BEST MANUAL CODE ON SQL SERVER
	t1=SECONDS()
	SELECT F1	;
		,(SELECT MIN(F2) FROM child1 WHERE F1=MASTER.F1) min1F2	;
		,(SELECT MIN(F2) FROM child2 WHERE F1=MASTER.F1) min2F2	;
	INTO CURSOR R2minmin9_3	;
	FROM MASTER
	? SECONDS()-t1,RECCOUNT(),'VFP9 ENGINE MASTER LOOP AND SUBQUERY TWO TABLES MIN'	

* NEXT IS THE COMMON PLAN CHOICE THAT SQL server Engine DO		
	t1=SECONDS()
	SELECT NVL(child1.F1,child2.F1) F1,child1.maxF2 min1F2,child2.maxF2 min2F2	;
	INTO CURSOR R2minmin9_1	;
	FROM ;
	(SELECT F1,MIN(F2) maxF2 FROM child1 GROUP BY F1) child1 ;
	FULL JOIN ;
	(SELECT F1,MIN(F2) maxF2 FROM child2 GROUP BY F1) child2 ;
	ON child1.F1=child2.F1
	
	? SECONDS()-t1,RECCOUNT(),'VFP9 ENGINE (SUBGROUP) FULL JOIN TWO TABLES MIN'

ENDIF