Tip to optimize SQL query

VFP has a very fast data engine. However, it could be slow if your SQL statement is not written efficiently.

I have written a simple test program here. Try run this program, record the elapse time, close VFP application and run it again for couple of times.

LOCAL lnCount1, lnCount2, lcUnique, lnSecond



CREATE CURSOR myTable1 (myField1 C(10))

CREATE CURSOR myTable2 (myField2 C(10))



FOR lnCount = 1 TO 100000

  lcUnique = SYS(2015)



  INSERT INTO myTable1 VALUES (lcUnique)



    FOR lnCount2 = 1 TO 10

      INSERT INTO myTable2 VALUES (lcUnique)

    NEXT

NEXT



SELECT myTable1

INDEX ON myField1 TAG myField1

SELECT myTable2

INDEX ON myField2 TAG myField2



CLEAR

lnSecond = SECOND()



*-- This SQL is most commonly use

SELECT * ;

FROM myTable1 INNER JOIN myTable2 ON ;

    myField1 == myField2 ;

WHERE myField1 == lcUnique ;

INTO CURSOR myTemp



?SECOND() - lnSecond



lnSecond = SECOND()



*-- This is "optimized" SQL

*-- Break the SQL into two, filter out one of these table first,

*-- then only join them up.

SELECT * ;

FROM myTable2 ;

WHERE myField2 == lcUnique ;

INTO CURSOR csrTemp



SELECT * ;

FROM csrTemp INNER JOIN myTable1 ;

  ON myField2 == myField1 ;

INTO CURSOR csrTemp



?SECOND() - lnSecond

You may found that, the time taken by commonly used SQL is much longer than the optimized.
Here is the result in my test.
Common Use SQL : 0.189
Optimized SQL : 0.001 ~ 0.002