taylorsharpe

JOINS in ExecuteSQL

Discussion created by taylorsharpe on Jun 11, 2017
Latest reply on Jul 12, 2017 by beverly

FileMaker's SQL process can be very fast for straight forward SELECTS, but when you add JOINS, it can become very slow.  And how you do things can make a big difference.

 

For example, doing a SELECT based on the larger table in a JOIN is better than basing it on the smaller table:

 

 

Based on the Smaller Table:

 

Let ( [

 

F1 = Get ( CurrentTimeUTCMilliseconds ) ;

F2 = "SELECT

       TSUD.\"Taxpayer Name\",

       TSUD.\"NAICS Code\"

  FROM

       NAICS

  LEFT JOIN

       TxSalesUseDatabase AS TSUD

  ON

       NAICS.NAICS = TSUD.\"NAICS Code\"

  WHERE

       TSUD.\"Location Zip Code\" = ? AND

       NAICS.NAICS LIKE ?" ;

F3 = ExecuteSQL ( F2 ; " | " ; ¶ ; 75209 ; "4%" ) ;

F4 = Get ( CurrentTimeUTCMilliseconds ) ;

F5 = ( F4 - F1 ) / 1000

 

] ;

 

F5

 

)

 

RESULT:

11.866  <====== Seconds

 

 

Based on Larger Table

 

Let ( [

 

F1 = Get ( CurrentTimeUTCMilliseconds ) ;

F2 = "SELECT

       TSUD.\"Taxpayer Name\",

       TSUD.\"NAICS Code\"

  FROM

       TxSalesUseDatabase AS TSUD

  LEFT JOIN

       NAICS

  ON

       NAICS.NAICS = TSUD.\"NAICS Code\"

  WHERE

       TSUD.\"Location Zip Code\" = ? AND

       NAICS.NAICS LIKE ?" ;

F3 = ExecuteSQL ( F2 ; " | " ; ¶ ; 75209 ; "4%" ) ;

F4 = Get ( CurrentTimeUTCMilliseconds ) ;

F5 = ( F4 - F1 ) / 1000

 

] ;

 

F5

 

)

 

RESULT:

.540  <====== ONLY 5 Tenths of a Second

 

 

 

NOW FOR THE SURPRISE!

 

If you don't use a JOIN and you instead do TWO SQL's and an "IN" Statement, you get fast performance:

 

Let ( [

 

F1 = Get ( CurrentTimeUTCMilliseconds ) ;

F2 = "SELECT

       \"NAICS\"

  FROM

       NAICS

  WHERE

       \"NAICS\" LIKE ?" ;

F3 = ExecuteSQL ( F2 ; ¶ ; "', '" ; "4%" ) ;

 

F4 = "SELECT

       \"Taxpayer Name\",

       \"NAICS Code\"

  FROM

       TxSalesUseDatabase

  WHERE

       \"Location Zip Code\" = ? and

       \"NAICS Code\" IN ( '" & F3 & "' )" ;

F5 = ExecuteSQL ( F4 ; " | " ; ¶ ; 75209 ) ;

 

F6 = Get ( CurrentTimeUTCMilliseconds ) ;

F7 = ( F6 - F1 ) / 1000

 

] ;

 

F7

 

)

 

RESULT:

.634   <===== 6 Tenths of a Second!!!

 

 

CONCLUSION:

 

Really try to avoid JOINs and if you can do it with multiple SQL's and an IN statement, it probably is faster than a Join, especially if it is based on the Smaller of the two tables in a JOIN!

 

 

FYI, the Large table has 739364 records and the small table has 3195 records.

Outcomes